我有一个查询,在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';

其他回答

用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子句中引用的列不能更新。

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`

下面的语法适合我。

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;

对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'
;

只是作为一个完整的问题,因为我们谈论的是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;
/