现有SQL语句
- SQL code
UPDATE (SELECT a.volumeid, a.volume, a.newcasenum, b.casenum, b.volumeid AS str FROM volume a, propdat b WHERE a.volumeid IS NULL AND a.volume = b.casenum) SET volumeid = str
括号里查询结果显示为
VOLUMEID VOLUME NEWCASENUM CASENUM STR
null F-7-1277 9610135046 F-7-1277 656424162
null F-7-1277 9610135046 F-7-1277 656424162
null F-7-1277 9610135046 F-7-1277 656424162
null F-7-1277 9610135046 F-7-1277 656424162
null F-7-1277 9610135046 F-7-1277 656424162
null F-7-1277 9610135046 F-7-1277 656424162
现在想把str的数值赋值到volumeid里 报错
报错提示为cannot modify a column which maps to a non key-preserved tabl
求大神们给个可以解决方法或代码的SQL语句 怎么才能蒋STR里的值赋值到volumeid里 谢谢
------解决方案--------------------
试试这个呢
- SQL code
UPDATE volume a SET volumeid = (SELECT b.volumeid FROM propdat b WHERE a.volumeid IS NULL AND a.volume = b.casenum AND ROWNUM = 1) WHERE EXISTS (SELECT 1 FROM propdat c WHERE a.volumeid IS NULL AND a.volume = c.casenum)