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

其他回答

用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 ( SELECT t1.value, t2.CODE
          FROM table1 t1
          INNER JOIN table2 t2 ON t1.Value = t2.DESC
          WHERE t1.UPDATETYPE='blah')
 SET t1.Value= t2.CODE

对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中无效。你可以这样做:

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是否认为内联视图是可更新的 (第二个语句是否可更新取决于列出的一些规则 这里)。

如这里所示,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
                         )

我认为这很有趣,特别是当你想要更新多个字段时。