Update rows in one table with data from another table based on one column in each being equal

Question

Update many rows into one table from another table based on one column in each being equal (user_id).

both tables have a user_id column. Need to insert data from t2 into t1 when the user_id column are equal.

Upvoted. See related question: http://stackoverflow.com/q/7030699/435605 — AlikElzin-kilaka, Mar 11 '15 at 12:14

Dimitre Radoulov · Accepted Answer · 2015-03-11T12:53:09.993

51

update 
  table1 t1
set
  (
    t1.column1, 
    t1.column2
      ) = (
    select
      t2.column1, 
      t2.column2
    from
      table2  t2
    where
      t2.column1 = t1.column1
     )
    where exists (
      select 
        null
      from 
        table2 t2
      where 
        t2.column1 = t1.column1
      );

Or this (if t2.column1 <=> t1.column1 are many to one and anyone of them is good):

update 
  table1 t1
set
  (
    t1.column1, 
    t1.column2
      ) = (
    select
      t2.column1, 
      t2.column2
    from
      table2  t2
    where
      t2.column1 = t1.column1
    and
      rownum = 1    
     )
    where exists (
      select 
        null
      from 
        table2 t2
      where 
        t2.column1 = t1.column1
      );

edited Mar 11 '15 at 12:53

answered Oct 27 '11 at 17:08

Dimitre Radoulov

27,252
4
40
48

Thank you very much. Works like a charm after data corrected to eliminate duplicates!!! – JcR49 Oct 27 '11 at 19:27
There are a lot of other answers for this elsewhere in SO but this is the only one that worked for me, and it saved my bacon. Thank you! – Ian Jul 09 '14 at 16:04
1

This answer updates more rows than needed. An additional `where` is needed. See answer by Tony Andrews. – AlikElzin-kilaka Mar 11 '15 at 12:16
@AlikElzin-kilaka, corrected, thanks for pointing out the mistake! – Dimitre Radoulov Mar 11 '15 at 12:53

score 25 · Answer 2 · answered Oct 27 '11 at 16:01

25

If you want to update matching rows in t1 with data from t2 then:

update t1
set (c1, c2, c3) = 
(select c1, c2, c3 from t2
 where t2.user_id = t1.user_id)
where exists
(select * from t2
 where t2.user_id = t1.user_id)

The "where exists" part it to prevent updating the t1 columns to null where no match exists.

answered Oct 27 '11 at 16:01

Tony Andrews

129,880
21
220
259

1

Thank you. Like other queries I have tried, this returns 'single row query rturns many rows'.. Will not insert/update anything. – JcR49 Oct 27 '11 at 16:11
1

So user_id is not unique in t1? In that case, how do you know **which** t1 to get the values from? – Tony Andrews Oct 27 '11 at 16:30
Yes, there is only one each user_id. Both tables have one column of user_id and have matching entries, but there is only one user_id, per user.... – JcR49 Oct 27 '11 at 16:33
The "single row query returns many rows" error means that for some user_id there are 2 (or more) rows in t2. So either your last comment was wrong or your database is corrupted! – Tony Andrews Oct 27 '11 at 16:37
Thank you..... Should be only one... I should have stated I will look more carefully. I do appreciate the help. – JcR49 Oct 27 '11 at 16:43
Not sure I am operatoing this correctly....This answer is correct and very helpful. Thank you. – JcR49 Oct 27 '11 at 20:37
The Where Exists is an important part especially if you are updating from a smaller table which only has a subset of the data into a much larger table. Significant speed boost. – AnthonyVO Oct 10 '14 at 18:34

score 14 · Answer 3 · edited Feb 27 '15 at 11:05

14

merge into t2 t2 
using (select * from t1) t1
on (t2.user_id = t1.user_id)
when matched then update
set
   t2.c1 = t1.c1
,  t2.c2 = t1.c2

edited Feb 27 '15 at 11:05

ky4k0b

107
1
1
8

answered Oct 28 '11 at 08:48

schurik

7,798
2
23
29

score 7 · Answer 4 · answered Oct 27 '11 at 16:01

7

It's not an insert if the record already exists in t1 (the user_id matches) unless you are happy to create duplicate user_id's.

You might want an update?

UPDATE t1
   SET <t1.col_list> = (SELECT <t2.col_list>
                          FROM t2
                         WHERE t2.user_id = t1.user_id)
 WHERE EXISTS
      (SELECT 1
         FROM t2
        WHERE t1.user_id = t2.user_id);

Hope it helps...

answered Oct 27 '11 at 16:01

Ollie

17,058
7
48
59

ORA-01427: single-row subquery returns more than one row – JcR49 Oct 27 '11 at 16:21
I keep coming back to the same ora 01427 single row query returns more than one row. – JcR49 Oct 27 '11 at 16:23
You will get an ORA-01427 error if the relationship is not one to one. That means your equijoin statement cannot successfully match one record in table one to one record in table two. – Nathan Mar 10 '16 at 17:47

score 2 · Answer 5 · edited Oct 31 '14 at 12:14

You Could always use and leave out the "when not matched section"

merge into table1 FromTable   
   using table2 ToTable
     on     ( FromTable.field1 = ToTable.field1
          and  FromTable.field2 =ToTable.field2)
when Matched then
update set 
  ToTable.fieldr = FromTable.fieldx,
  ToTable.fields = FromTable.fieldy, 
  ToTable.fieldt =  FromTable.fieldz)
when not matched then
  insert  (ToTable.field1,
       ToTable.field2,
       ToTable.fieldr,
       ToTable.fields,
       ToTable.fieldt)
  values (FromTable.field1,
         FromTable.field2,
         FromTable.fieldx,
         FromTable.fieldy,
         FromTable.fieldz);

Update rows in one table with data from another table based on one column in each being equal

5 Answers5

Linked

Related