CODEWITHSUNDEEP
sqloracle-database
Stephane B.
Stephane B.

Reputation: 582

Insert from multiple tables, multiple rows

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

Upvotes: 1

Views: 106

Answers (3)

Karthikeyani Anandhan
Karthikeyani Anandhan

Reputation: 314

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

Upvotes: 0

forpas
forpas

Reputation: 164184

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'

Upvotes: 1

Chaitanya Kotha
Chaitanya Kotha

Reputation: 476

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

Upvotes: 4

Related Questions