sql - 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.

Thank you in advance for any help offered.

question from:https://stackoverflow.com/questions/7918688/update-rows-in-one-table-with-data-from-another-table-based-on-one-column-in-eac

Answer 1

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
      );