CODEWITHSUNDEEP
sqldatabaseoracle-database
point71echo
point71echo

Reputation: 330

SQL - Add column data from one table into another while preserving original data

I need to add data from one table (table1) into another table (table2) WHERE the data in the fullname column matches in both tables. The code below almost does what I want, except that it deletes all of the other data in table1's title column. 

UPDATE table1
SET title = (SELECT title
               FROM table2
             WHERE table2.fullname = table1.fullname)

My goal is to update table1's title column to have both the data it originally had plus the data from table2's title column without erasing the data that was in table1's title column prior to running the SQL query.

Upvotes: 1

Views: 165

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

In Oracle you can use a Merge:

MERGE INTO table1 t1
USING (SELECT fullname, title
       FROM   table2) t2
ON t1.fullname = t2.fullname
WHEN MATCHED THEN
  UPDATE SET t1.title = t2.title;

This will only update the rows where a match on fullname is found.

Upvotes: 2

David Faber
David Faber

Reputation: 12486

I'm assuming you're using Oracle given the syntax you've given. The issue is that, when you use this form of the UPDATE statement, you also need a WHERE EXISTS clause or something similar:

UPDATE table1
   SET title = ( SELECT title
                   FROM table2
                  WHERE table2.fullname = table1.fullname )
 WHERE EXISTS ( SELECT 1 FROM table2
                  WHERE table2.fullname = table1.fullname )

Otherwise the non-matching titles will get NULLed out! The reason for this is that the result of the subquery will be NULL when fullname doesn't exist in table2.

If concatenation is what you're looking for (as @PM 77-1 comments above), then you'll want to do something like the following:

UPDATE table1
   SET title = title || ',' || ( SELECT title
                                   FROM table2
                                  WHERE table2.fullname = table1.fullname )
 WHERE EXISTS ( SELECT 1 FROM table2
                  WHERE table2.fullname = table1.fullname )

Hope this helps.

Upvotes: 2

Related Questions