一、mysql:
注意:此处的 inner join 不可以像往常一样使用逗号“,”替换!!!切记!!
update table1 inner join table2 on table1.id=table2.pid
set table1.`name`= table2.`name`
备注:
可以把上面的 table1 inner join table2 on table1.id=table2.pid 看成是一个“整体表”,便于理解。即,相当于:
update table1 inner join table2 on table1.id=table2.pid set table1.`name`= table2.`name` | update (整体表) set ... |
二、SQL Server:
update table1 set table1.`name`= table2.`name` from table2
where table1.id=table2.pid
只有sql server支持 update ... from 啊 ~ ~,真浪费 ~
三、oracle:
创建如下表数据
select * from t1 ;
select * from t2;
现需求:参照T2表,修改T1表,修改条件为两表的fname列内容一致。
方式1,update
常见陷阱:
UPDATE T1
SET T1.FMONEY = (select T2.FMONEY from t2 where T2.FNAME = T1.FNAME)
执行后T1结果如下:
有一行原有值,被更新成空值了。
正确写法:
UPDATE T1
SET T1.FMONEY = (select T2.FMONEY from t2 where T2.FNAME = T1.FNAME)
WHERE EXISTS(SELECT 1 FROM T2 WHERE T2.FNAME = T1.FNAME);
方式2:内联视图更新
UPDATE (
select t1.fmoney fmoney1,t2.fmoney fmoney2 from t1,t2 where t1.fname = t2.fname
)t
set fmoney1 =fmoney2;
方式3:merge更新
merge into t1
using (select t2.fname,t2.fmoney from t2) t
on (t.fname = t1.fname)
when matched then
update set t1.fmoney = t.fmoney;