我有一个查询,在MySQL工作得很好,但当我在Oracle上运行它时,我得到以下错误:
SQL错误:ORA-00933: SQL命令未正确结束 00933. 00000 - "SQL命令未正确结束"
查询为:
UPDATE table1
INNER JOIN table2 ON table1.value = table2.DESC
SET table1.value = table2.CODE
WHERE table1.UPDATETYPE='blah';
我有一个查询,在MySQL工作得很好,但当我在Oracle上运行它时,我得到以下错误:
SQL错误:ORA-00933: SQL命令未正确结束 00933. 00000 - "SQL命令未正确结束"
查询为:
UPDATE table1
INNER JOIN table2 ON table1.value = table2.DESC
SET table1.value = table2.CODE
WHERE table1.UPDATETYPE='blah';
当前回答
用这个:
MERGE
INTO table1 trg
USING (
SELECT t1.rowid AS rid, t2.code
FROM table1 t1
JOIN table2 t2
ON table1.value = table2.DESC
WHERE table1.UPDATETYPE='blah'
) src
ON (trg.rowid = src.rid)
WHEN MATCHED THEN UPDATE
SET trg.value = code;
其他回答
只是作为一个完整的问题,因为我们谈论的是Oracle,这也可以做到:
declare
begin
for sel in (
select table2.code, table2.desc
from table1
join table2 on table1.value = table2.desc
where table1.updatetype = 'blah'
) loop
update table1
set table1.value = sel.code
where table1.updatetype = 'blah' and table1.value = sel.desc;
end loop;
end;
/
如这里所示,Tony Andrews提出的第一个解决方案的通用语法是:
update some_table s
set (s.col1, s.col2) = (select x.col1, x.col2
from other_table x
where x.key_value = s.key_value
)
where exists (select 1
from other_table x
where x.key_value = s.key_value
)
我认为这很有趣,特别是当你想要更新多个字段时。
对table2使用description而不是desc,
update
table1
set
value = (select code from table2 where description = table1.value)
where
exists (select 1 from table2 where description = table1.value)
and
table1.updatetype = 'blah'
;
它工作得很好
merge into table1 t1
using (select * from table2) t2
on (t1.empid = t2.empid)
when matched then update set t1.salary = t2.salary
该语法在Oracle中无效。你可以这样做:
UPDATE table1 SET table1.value = (SELECT table2.CODE
FROM table2
WHERE table1.value = table2.DESC)
WHERE table1.UPDATETYPE='blah'
AND EXISTS (SELECT table2.CODE
FROM table2
WHERE table1.value = table2.DESC);
或者你可以这样做:
UPDATE
(SELECT table1.value as OLD, table2.CODE as NEW
FROM table1
INNER JOIN table2
ON table1.value = table2.DESC
WHERE table1.UPDATETYPE='blah'
) t
SET t.OLD = t.NEW
这取决于Oracle是否认为内联视图是可更新的 (第二个语句是否可更新取决于列出的一些规则 这里)。