使用来自另一个表的数据更新一个表中的行，基于每个表中的一列相等

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

基于每个表中的一个列相等（user_id），将多个行从另一个表更新为一个表。

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

两个表都有一个user_id列。当user_id列相等时，需要将数据从t2插入到t1。

Thank you in advance for any help offered.

提前感谢您提供的任何帮助。

5 个解决方案

#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):

或者这个（如果t2.column1 <=> t1.column1是多对一的，其中任何一个都是好的）：

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

#2

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

如果要使用t2中的数据更新t1中的匹配行，则：

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.

“where exists”部分用于防止在不存在匹配的情况下将t1列更新为null。

#3

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

#4

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.

如果记录已存在于t1（user_id匹配）中，则它不是插入，除非您乐意创建重复的user_id。

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

希望能帮助到你...

#5

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

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