CODEWITHSUNDEEP
oracle-database
user3867590
user3867590

Reputation: 37

How do I insert values in a table using inner join?

I am trying to insert data into table1.col1 using following query.

INSERT INTO table1 t1( t1.col1)  
SELECT t2.col1
FROM table2 t2
WHERE t1.col2= t2.col2;

Apparently, it wouldn't work(flawed logic maybe). How can I achieve similar results. Let me know if I don't make sense.

Upvotes: 0

Views: 91

Answers (4)

user3867590
user3867590

Reputation: 37

So, I was not looking to insert but to update...stupid question I know :) This is what I was looking for.

update table1 t1 set t1.col1 = (select t2.col1 from table2 t2 where t1.col2 = t2.col2);

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

It seems you need a MERGE statement with MATCHED(for already existing rows in table1) and
NOT MATCHED(for rows not inserted into table1 yet) options :

MERGE INTO table1 t1
  USING table2 t2
    ON (t1.col2 = t2.col2)
  WHEN MATCHED THEN
    UPDATE SET t1.col1 = t2.col1
  WHEN NOT MATCHED THEN
    INSERT (col1,col2)
    VALUES (t2.col1, t2.col2);

Demo

Upvotes: 0

Mohd Ahmad
Mohd Ahmad

Reputation: 55

INSERT INTO table1 (col1)
SELECT t2.col1 FROM table1 t1,table2 t2 WHERE t1.col2= t2.col2;

Upvotes: 0

Nat
Nat

Reputation: 670

INSERT INTO table1 (col1)
SELECT t2.col1 FROM table2 t2 INNER JOIN table1 t1 on t1.col2= t2.col2;

Upvotes: 1

Related Questions