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

User · Answer

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

User · Answer

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

User · Answer

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  lt    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

User · Answer

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

User · Answer

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  lt t1 col list gt     SELECT  lt t2 col list gt                            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

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

Examples related to sql

Examples related to oracle

Examples related to sql-update