我有一个查询,在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';
当前回答
只是作为一个完整的问题,因为我们谈论的是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;
/
其他回答
只是作为一个完整的问题,因为我们谈论的是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;
/
用WHERE子句合并:
MERGE into table1
USING table2
ON (table1.id = table2.id)
WHEN MATCHED THEN UPDATE SET table1.startdate = table2.start_date
WHERE table1.startdate > table2.start_date;
您需要WHERE子句,因为ON子句中引用的列不能更新。
它工作得很好
merge into table1 t1
using (select * from table2) t2
on (t1.empid = t2.empid)
when matched then update set t1.salary = t2.salary
UPDATE (SELECT T.FIELD A, S.FIELD B
FROM TABLE_T T INNER JOIN TABLE_S S
ON T.ID = S.ID)
SET B = A;
A和B是别名字段,不需要指向表。
下面的语法适合我。
UPDATE
(SELECT A.utl_id,
b.utl1_id
FROM trb_pi_joint A
JOIN trb_tpr B
ON A.tp_id=B.tp_id Where A.pij_type=2 and a.utl_id is null
)
SET utl_id=utl1_id;