我有一个查询,在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';
当前回答
UPDATE table1 t1
SET t1.value =
(select t2.CODE from table2 t2
where t1.value = t2.DESC)
WHERE t1.UPDATETYPE='blah';
其他回答
update table1 a
set a.col1='Y'
where exists(select 1
from table2 b
where a.col1=b.col1
and a.col2=b.col2
)
用这个:
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;
UPDATE IP_ADMISSION_REQUEST ip1
SET IP1.WRIST_BAND_PRINT_STATUS=0
WHERE IP1.IP_ADM_REQ_ID =
(SELECT IP.IP_ADM_REQ_ID
FROM IP_ADMISSION_REQUEST ip
INNER JOIN VISIT v
ON ip.ip_visit_id=v.visit_id
AND v.pat_id =3702
); `enter code here`
只是作为一个完整的问题,因为我们谈论的是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;
/
不要使用上面的一些答案。
有些人建议使用嵌套SELECT,不要这样做,它非常慢。如果你有很多记录要更新,使用join,就像这样:
update (select bonus
from employee_bonus b
inner join employees e on b.employee_id = e.employee_id
where e.bonus_eligible = 'N') t
set t.bonus = 0;
更多细节请参见此链接。 http://geekswithblogs.net/WillSmith/archive/2008/06/18/oracle-update-with-join-again.aspx。
另外,确保所有要连接的表上都有主键。