table1"
col1 col2 col3
A1 2000
A2 1800
A3 5000 2005-10-02
A4 3000
A5 4000
table2
col1 col2 col3
A1 小王 2005-11-02
A2 小张 2005-11-05
A3 小张 2005-10-02
A4 小钱 2005-01-02
A5 小孙 2005-12-02
table1和table2用col1关联
请用SQL更新table1表中的col3,条件是table1中col3为空的记录,更新为记录对应table2的col3记录;
即要的最后结果为:
table1"
col1 col2 col3
A1 2000 2005-11-02
A2 1800 2005-11-05
A3 5000 2005-10-02
A4 3000 2005-01-02
A5 4000 2005-12-02
col1 col2 col3
A1 2000
A2 1800
A3 5000 2005-10-02
A4 3000
A5 4000
table2
col1 col2 col3
A1 小王 2005-11-02
A2 小张 2005-11-05
A3 小张 2005-10-02
A4 小钱 2005-01-02
A5 小孙 2005-12-02
table1和table2用col1关联
请用SQL更新table1表中的col3,条件是table1中col3为空的记录,更新为记录对应table2的col3记录;
即要的最后结果为:
table1"
col1 col2 col3
A1 2000 2005-11-02
A2 1800 2005-11-05
A3 5000 2005-10-02
A4 3000 2005-01-02
A5 4000 2005-12-02
实现:
Update t1 set t1.col3=(select t2.col3 from t2 where t1.col1=t2.col1)
where t1.col3 is null
更严谨的做法是:Update t1 set t1.col3=(select t2.col3 from t2 where t1.col1=t2.col1)
where trim(t1.col3) is null
where t1.col3 is null
更严谨的做法是:Update t1 set t1.col3=(select t2.col3 from t2 where t1.col1=t2.col1)
where trim(t1.col3) is null
更更严谨的做法是:
Update t1 set t1.col3=(select t2.col3 from t2 where t1.col1=t2.col1)
where trim(t1.col3) is null
and exists (select 1 from t2 where t2.col1 = t1.col1);
t1, t2 中的 col1 还必须是主键
Update t1 set t1.col3=(select t2.col3 from t2 where t1.col1=t2.col1)
where trim(t1.col3) is null
and exists (select 1 from t2 where t2.col1 = t1.col1);
t1, t2 中的 col1 还必须是主键