创建如下数据:
select * from t1;
FNAME | FMONEY |
---|---|
A | 20 |
B | 30 |
select * from t2;
FNAME | FMONEY |
---|---|
A | 100 |
C | 40 |
D | 50 |
需求:参考 T2,修改 T1 表,修改条件为两表的 FNAME 列内容一致。
方式1,update
常见陷阱:
UPDATE T1
SET T1.FMONEY = (select T2.FMONEY from t2 where T2.FNAME = T1.FNAME)
执行后,T1表数据如下(错误!!!):
FNAME | FMONEY |
---|---|
A | 100 |
B | 空了!!!! |
正确写法:
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);
执行后,T1表数据如下(正确):
FNAME | FMONEY |
---|---|
A | 100 |
B | 30 |
注意:
必须加最后一行WHERE EXISTS(SELECT 1 FROM T2 WHERE T2.FNAME = T1.FNAME)
判断
避免将 t1 不存在 t2 的字段更新为空值:上述测试数据 t1.B 就被更新为空了,这是不对的。
方式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;