Insert from multiple tables, multiple rows

Question

I have been trying to solve my problem but could not find the answer. Oracle db.

I have table context with looks like:

| contextId | customer |
 ----------------------
|         1 | John     |
|         2 | David    |

I have another table setting:

| contextId | settingName | settingValue |
 ----------------------------------------
|         1 | Happiness   |            6 |
|         1 | Sadness     |            3 |

What I would like is to insert David in setting, while copying the settingName and settingValue of John (contextId = 1).

Result should look like:

| contextId | settingName | settingValue |
 ----------------------------------------
|         1 | Happiness   |            6 |
|         1 | Sadness     |            3 |
|         2 | Happiness   |            6 |
|         2 | Sadness     |            3 |

Thanks

Chaitanya Kotha · Accepted Answer · 2019-05-06 13:04:02Z

4

INSERT INTO SETTING
  SELECT (SELECT CONTEXTID FROM CONTEXT WHERE CUSTOMER = 'DAVID'),
         SETTINGNAME,
         SETTINGVALUE
    FROM SETTING
   WHERE CONTEXTID = 1

answered May 6, 2019 at 13:04

Chaitanya Kotha

4762 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

forpas · Accepted Answer · 2019-05-06 13:16:33Z

1

If your requirement is to use the customer names 'David' and 'John', then you need a join:

INSERT INTO SETTING (CONTEXTID, SETTINGNAME, SETTINGVALUE)
  SELECT 
    (SELECT CONTEXTID FROM CONTEXT WHERE CUSTOMER = 'David'),
    s.SETTINGNAME,
    s.SETTINGVALUE
  FROM CONTEXT AS c INNER JOIN SETTING AS s 
  ON s.CONTEXTID = c.CONTEXTID
  WHERE c.CUSTOMER = 'John'

answered May 6, 2019 at 13:16

forpas

165k10 gold badges51 silver badges85 bronze badges

2 Comments

Stephane B. Over a year ago

This is actually what I needed because context id is different depending on the environments this query is run (but the values are the same). That's why I mark this answer as accepted. But the answer of Chaitanya Kotha is also good.

Stephane B. Over a year ago

Note that I had to remove 'AS' from the query for it to work though.

Karthikeyani Anandhan · Accepted Answer · 2019-05-06 13:17:18Z

0

Declared a separate variable to hold the contextId of customer - David. And inserting the multiple values available in setting table for customer - David.

DECLARE
  l_contextId context.contextId%TYPE;
BEGIN

  SELECT
    contextId
  INTO
    l_contextId
  FROM
    context
  WHERE
    customer = 'David';

END; 


INSERT INTO setting
(contextId, settingName, settingValue)
SELECT l_contextId, settingName, settingValue
FROM setting
WHERE contextId = 1

answered May 6, 2019 at 13:17

Karthikeyani Anandhan

3341 silver badge6 bronze badges

Collectives™ on Stack Overflow

Insert from multiple tables, multiple rows

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related