批量更新update语句怎么写？

[xiwu]

有两个表table1,table2，都有相同字段 字段ID、字段NAME。
我想update更新table2中字段NAME的数据，使tabke2.NAME = table1.NAME，
只更新tabke2.ID= table1.ID的数据，其他不修改。
oracle的update语句怎么写？

table1
字段ID 字段NAME 字段D
2 faf
3 kjk
4 pop


table2
字段ID 字段NAME 字段C
1 jjfh
2 iuauyf
3 ow
4 oer
5 pwrof
6 ofie

[sy_ivysunlenovo]

update table1 t1,table2 t2 set t2.NAME = t1.NAME
 where t1.ID=t2.ID

[luoyoumou]

update table2 t2
  SET t2.NAME=(SELECT t1.NAME FROM table1 t1
                   WHERE t1.ID=t2.ID)
  WHERE EXISTS (SELECT NULL
                FROM table1 t3
                WHERE t3.ID=t2.ID);

[sunnylonglong]

[Quote=引用 1 楼 的回复:]
SQL code


update table1 t1,table2 t2 set t2.NAME = t1.NAME
where t1.ID=t2.ID
[/Quote]
~~~

[laolaodai]

update table1 t1,table2 t2 set t2.NAME = t1.NAME
where t1.ID=t2.ID

[ORAClE SE]

UPDATE table2
SET NAME = (SELECT NAME FROM table1 WHERE table2.Id = table1.Id)
WHERE EXISTS (SELECT 1 FROM table1 WHERE table2.Id = table1.Id)

[ORAClE SE]

UPDATE table2
SET NAME = (SELECT NAME FROM table1 WHERE table2.Id = table1.Id)
WHERE EXISTS (SELECT 1 FROM table1 WHERE table2.Id = table1.Id)

[allen519]

update table2 t2
SET t2.NAME=(SELECT t1.NAME FROM table1 t1
WHERE t1.ID=t2.ID)
WHERE EXISTS (SELECT NULL
FROM table1 t3
WHERE t3.ID=t2.ID);


UPDATE table2
SET NAME = (SELECT NAME FROM table1 WHERE table2.Id = table1.Id)
WHERE EXISTS (SELECT 1 FROM table1 WHERE table2.Id = table1.Id)


这两种方式都可行么？？
为什么一个是t3 一个是t1 呢？？

[luckings]

update table2 set  name=(select name from table1 t where t.id=table2.id) where exists(select 1 from table1 z where z.id=table2.id);

[GG_wg]

[Quote=引用 2 楼 的回复:]
SQL code
update table2 t2
SET t2.NAME=(SELECT t1.NAME FROM table1 t1
WHERE t1.ID=t2.ID)
WHERE EXISTS (SELECT NULL
FROM table1 t2
WHERE t2.ID = t1.ID
[/Quote]

2楼t3改为t2就行了

[zhouda_Leon]

引用 1 楼 sy_ivysunlenovo 的回复:

SQL code update table1 t1,table2 t2 set t2.NAME = t1.NAME where t1.ID=t2.ID

怎么提示缺少set关键字呢

[jianglili130]

UPDATE table2 SET NAME = (SELECT NAME FROM table1 WHERE table2.Id = table1.Id) WHERE EXISTS (SELECT 1 FROM table1 WHERE table2.Id = table1.Id) ;

[善若止水]

建议楼主用merge函数，merge适用于批量的插入数据

[lanph]

使用触发器，一个表变化，引起另外表变化

[土匪八号]

数据量大的话要用游标的

[foolish_cc1986]

merge into table2 using table1 on (table2.id=table1.id)
when matched then update set table2.name=table1.name;

[freeman0526]

引用 12 楼 zhaoxiangchong 的回复:

建议楼主用merge函数，merge适用于批量的插入数据

MERGE 慎重.小心全表更新.

[善若止水]

引用 16 楼 freeman0526 的回复:

引用 12 楼 zhaoxiangchong 的回复:建议楼主用merge函数，merge适用于批量的插入数据 MERGE 慎重.小心全表更新.

谢谢楼主

[ajune]

update table2 set table2.name=table1.name where table2.id=table1.id

[zhouziyang_2010]

update table1 set name=(select name from table2 where table1.id=table2.id) where id in(select id from table2)

[wgywell]

MERGE INTO table1 D USING (SELECT * FROM table2) S ON(D.ID=S.ID) WHEN MATCHED THEN UPDATE SET D.NAME = S.NAME