我必须更新表中的字段.我正在使用以下查询.帮助我,哪一个是对的?
update table1 set col1=,col2= .... from table1 t,table2 s where t.id=s.num and s.code='abc';
要么
update table1 set col1=,col2= .... where table1.id=table2.num and table2.code='abc';
哪一个是对的?或两者都不正确?请提出一些建议......
两者都不正确.从你的片段中不清楚你要做什么,但用另一个表更新一个表的语法更像是:
update table1 set (col1, col2) = ( select col1, col2 from table2 where table2.num = table1.id and table2.code = 'abc' ) where table1.id in (select num from table2);
最后的WHERE子句是为了防止使用null更新所有不匹配的table1行.
当table1 在查询中"密钥保留"时,另一种方法是:
update ( select table1.id, table1.col1, table1.col2 , table2.col1 as new_col1, table2.col as new_col2 from table1 join table2 on table2.num = table1.id ) set col1 = new_col1, col2 = new_col2;
MERGE
INTO table1 t1
USING (
SELECT *
FROM table2
WHERE table2.code = 'abc'
) t2
ON (t1.id = t2.num)
WHEN MATCHED THEN
UPDATE
SET col1 = t2.value1, col2 = t2.value2
请记住始终限定SET语句,即t2.value1.不明确时不会抛出任何错误.换句话说,SET col1 = value1将是t2.value1,但如果缺少t2.value1则为t1.value1.