By Muhd


2011-08-11 18:05:54 8 Comments

Table 1:

id    name    desc
-----------------------
1     a       abc
2     b       def
3     c       adf

Table 2:

id    name    desc
-----------------------
1     x       123
2     y       345

In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using the same id? So the end result I would get is

Table 1:

id    name    desc
-----------------------
1     x       123
2     y       345
3     c       adf

Question is taken from update one table with data from another, but specifically for oracle SQL.

7 comments

@Adrian 2015-09-23 20:22:48

Try this:

MERGE INTO table1 t1
USING
(
-- For more complicated queries you can use WITH clause here
SELECT * FROM table2
)t2
ON(t1.id = t2.id)
WHEN MATCHED THEN UPDATE SET
t1.name = t2.name,
t1.desc = t2.desc;

@jefissu 2018-08-13 14:52:44

Very fast indeed, 1159477 rows merged in 15,5s

@Avila Theresa 2015-01-20 17:31:58

BEGIN
For i in (select id, name, desc from table2) 
LOOP
Update table1 set name = i.name, desc = i.desc where id = i.id and (name is null or desc is null);
END LOOP;
END;

@Pau Karr 2013-06-27 03:04:53

Update table set column = (select...)

never worked for me since set only expects 1 value - SQL Error: ORA-01427: single-row subquery returns more than one row.

here's the solution:

BEGIN
For i in (select id, name, desc from table1) 
LOOP
Update table2 set name = i.name, desc = i.desc where id = i.id;
END LOOP;
END;

That's how exactly you run it on SQLDeveloper worksheet. They say it's slow but that's the only solution that worked for me on this case.

@Pau Karr 2013-06-27 06:44:47

can somebody please explain why this deserves a -2 on reputation? LOL.

@Alternator 2013-08-06 04:34:45

I didn't down rate, but it isn't a good solution. Firstly: if the subselect was returning multiple values, then the for loop will be overwriting the name on table2 multiple times for some/all records (not clean). Secondly: there is no order by clause so this will occur in an unpredictable manner (i.e. last value in unordered data wins). Thirdly: It will be much slower. Assuming the outcome of the for loop was intended, the original subselect could have been rewritten in some controlled way to return only 1 value for each record... simplest contrived way would be (select min(name)...)

@Robert Hyatt 2014-09-19 19:42:40

This was exactly what I needed. Thanks (+1)

@Francis 2015-08-20 16:27:15

If you get multiple values in your subquery, you might rethink the query and use DISTINCT or GROUP BY with MIN, MAX. Just an idea.

@Ray K. 2016-01-07 16:20:20

Long story short: if you can at all avoid it, never ever EVER use any kind of LOOP in a T-SQL statement. Personally, if it wasn't for the 0.001% of the time where there's no other solution, I don't even think it should even be an available function in T-SQL. T-SQL is designed to be set-based, so it works on entire sets of data as a whole; it should NOT be used to work on data line-by-line.

@Demonblack 2018-01-17 12:39:44

This is terribly slow and inefficient. You didn't even filter table1! If table1 has 150 million records in it and table2 only has 10, you'lle cycle 150 million times for no reason at all. And even if you did filter it, it's still slow as hell for any large dataset. Use a MERGE statement instead.

@Jim P 2013-06-14 17:03:06

If your table t1 and it's backup t2 have many columns, here's a compact way to do it.

In addition, my related problem was that only some of the columns were modified and many rows had no edits to these columns, so I wanted to leave those alone - basically restore a subset of columns from a backup of the entire table. If you want to just restore all rows, skip the where clause.

Of course the simpler way would be to delete and insert as select, but in my case I needed a solution with just updates.

The trick is that when you do select * from a pair of tables with duplicate column names, the 2nd one will get named _1. So here's what I came up with:

  update (
    select * from t1 join t2 on t2.id = t1.id
    where id in (
      select id from (
        select id, col1, col2, ... from t2
        minus select id, col1, col2, ... from t1
      )
    )
  ) set col1=col1_1, col2=col2_1, ...

@Jon Heller 2013-06-14 23:05:08

This does not work for me in Oracle 11g. Can you create a working example of this method?

@ant 2012-10-03 11:40:32

Here seems to be an even better answer with 'in' clause that allows for multiple keys for the join:

update fp_active set STATE='E', 
   LAST_DATE_MAJ = sysdate where (client,code) in (select (client,code) from fp_detail
  where valid = 1) ...

The full example is here: http://forums.devshed.com/oracle-development-96/how-to-update-from-two-tables-195893.html

The beef is in having the columns that you want to use as the key in parentheses in the where clause before 'in' and have the select statement with the same column names in parentheses. where (column1,column2) in ( select (column1,column2) from table where "the set I want" );

@Justin Cave 2011-08-11 19:05:21

This is called a correlated update

UPDATE table1 t1
   SET (name, desc) = (SELECT t2.name, t2.desc
                         FROM table2 t2
                        WHERE t1.id = t2.id)
 WHERE EXISTS (
    SELECT 1
      FROM table2 t2
     WHERE t1.id = t2.id )

Assuming the join results in a key-preserved view, you could also

UPDATE (SELECT t1.id, 
               t1.name name1,
               t1.desc desc1,
               t2.name name2,
               t2.desc desc2
          FROM table1 t1,
               table2 t2
         WHERE t1.id = t2.id)
   SET name1 = name2,
       desc1 = desc2

@Mathias Bader 2013-08-05 07:53:17

In your first code example: Is the outer WHERE-clause necessary for correct results? Or do you use it only to speed up the query?

@Justin Cave 2013-08-05 15:16:44

@totoro - In the first example, the WHERE EXISTS prevents you from updating a row in t1 if there is no matching row in t2. Without it, every row in t1 will be updated and the values will be set to NULL if there is no matching row in t2. That is generally not what you want to happen so the WHERE EXISTS is generally needed.

@Andrew Leach 2013-12-17 16:12:39

It's worth adding that the SELECT ... FROM t2 must result in a unique row. This means that you have to select on all the fields which comprise a unique key -- a non-unique primary key is not sufficient. Without uniqueness, you are reduced to something like @PaulKarr's loop -- and if there is not a unique correlation, then more than one target row may be updated for each source row.

@Nathan Spears 2014-04-03 22:20:03

Would the WHERE EXISTS be better as WHERE t1.id IN (select id from t2) so you get a hash join instead of an EXISTS query on every row?

@Justin Cave 2014-04-03 22:23:14

@NathanSpears - It depends. Recent versions of Oracle are pretty good at producing the optimal plan whether you're using IN or EXISTS. If there is a difference, if you expect that the inline query is generally going to return a relatively large fraction of the rows in the target table, an EXISTS would generally be more efficient, an IN would generally be more efficient if you expect to return a relatively small fraction of the rows in the target table. Of course "small" and "large" are relative terms so the only real answer is to benchmark and see.

@Vadzim 2015-02-12 12:25:53

Explanation on key-preserved requirement for updatable joins: asktom.oracle.com/pls/asktom/…

@RachitSharma 2017-04-19 13:28:48

Thank you for sharing this wonderful answer. Just want to ask, i have a similar case and when using the above method(Correlated update) getting message that "Single-row subquery returns mre than one row". Can you please help? Thanks.

@Justin Cave 2017-04-20 18:41:58

@RachitSharma - That means that your subquery (the query from table2) is returning multiple rows for one or more table1 values and Oracle doesn't know which one you want to use. Normally, that means that you need to refine the subquery so that it returns a single distinct row.

@Toolkit 2018-05-31 13:51:11

wrong answer performance wise, there is MERGE command specifically for that

@Yahia 2011-08-11 18:08:49

try

UPDATE Table1 T1 SET
T1.name = (SELECT T2.name FROM Table2 T2 WHERE T2.id = T1.id),
T1.desc = (SELECT T2.desc FROM Table2 T2 WHERE T2.id = T1.id)
WHERE T1.id IN (SELECT T2.id FROM Table2 T2 WHERE T2.id = T1.id);

@David BalaĹžic 2017-11-21 15:44:41

The downside of this is that the SELECT statement is repeated 3 times. In complex examples that can be a deal-breaker.

Related Questions

Sponsored Content

22 Answered Questions

[SOLVED] Retrieving the last record in each group - MySQL

25 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2242598 View
  • 1524 Score
  • 25 Answer
  • Tags:   sql duplicates

20 Answered Questions

[SOLVED] SQL update from one Table to another based on a ID match

30 Answered Questions

[SOLVED] SQL select only rows with max value on a column

32 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

15 Answered Questions

[SOLVED] How can I do an UPDATE statement with JOIN in SQL?

17 Answered Questions

[SOLVED] How can I get column names from a table in SQL Server?

37 Answered Questions

19 Answered Questions

[SOLVED] Get list of all tables in Oracle?

  • 2008-10-15 17:54:49
  • vitule
  • 2002941 View
  • 1001 Score
  • 19 Answer
  • Tags:   sql oracle

6 Answered Questions

[SOLVED] SQL query return data from multiple tables

  • 2012-09-18 11:11:39
  • Fluffeh
  • 728754 View
  • 407 Score
  • 6 Answer
  • Tags:   mysql sql select

Sponsored Content