语句更新与Oracle Oracle错误上的Inner Join

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中查询工作

Answer 1

错误的语法。应该像这样：

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);

Answer 2

在Oracle

中查询工作

更新表1 设置CHK =（选择CHK 从表1 a 内联（选择substr（'0'|| inkdwil，-2）Wil，substr（chk，-4）站点，substr（'0'|| inkdwil，-2）|| substr（chk，-4）chkakhir，substr（'0'|| inkdwil，-2）||'0'chkdcust来自表2）b 在a.chk = b.site上）
其中长度（a.chk）= 4;