oracle - 在 Oracle 错误中使用内部联接更新语句

Question

我有一个在 Sybase 中运行良好的查询，但是当我在 Oracle 上运行它时出现以下错误

update table1
        set chk = b.chkakhir
    from table1 a
    inner join (select substr('0'||inkdwil,-2) wil,substr(chk,-4) site, wil||site chkakhir,wil||'0' chkdcust from table2) b
        on a.chk = b.site   
    where length(a.chk) = 4;

update table1
        set chk = b.chkakhir
    from table1 a
    inner join (select right('0'||inkdwil,2) wil,right(chk,4) site, wil||site chkakhir,wil||'0' chkdcust from table2) b
        on a.chk = b.site   
    where len(a.chk) = 4;

oracle中的查询工作

标签： oracle

Answer 1

就 Oracle 而言，语法错误。应该是这样的：

UPDATE table1
   SET chk      =
          (SELECT b.chkakhir               --> instead of FROM, you have to use SELECT here
             FROM table1 a
                  INNER JOIN (SELECT SUBSTR ('0' || inkdwil, -2) wil,
                                     SUBSTR (chk, -4) site,
                                     wil || site chkakhir,
                                     wil || '0' chkdcust
                                FROM table2) b
                     ON a.chk = b.site
            WHERE LENGTH (a.chk) = 4);