Left Join On Update ORA-00971: missing SET keyword SQL

Question

I'm getting a confusing ORA97100 missing SET keyword error when trying to run this simple UPDATE statement.

UPDATE 
    WFINANCE.RKAP_PROYEKSI a
LEFT JOIN 
    WFINANCE.RKAP_MASTER_KODE b ON b.ID_KODE = a.KODE_ANGGARAN
SET 
    a.STATUS = 'Waiting Approval',
    a.T_ID = 'TR1234'
WHERE 
    a.TAHUN = '2018'
AND
    b.KET_KD_GAS = 'BJU'

I've also tried to use the "AS", but still get in error.

Any well thought to advise will be appreciated.

Thanks

This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Sep 11 '19 at 07:23
Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental. And otherwise, it isn't minimal.) Here you could have chopped code until you got something that worked. But also always read the manual including grammar & examples when you use new or unfamiliar syntax. That is part of "research". — philipxy, Sep 11 '19 at 07:26

score 1 · Answer 1 · edited Sep 19 '19 at 21:13

First - there's no point making the join of RKAP_MASTER_KODE an outer join. The presence of b.KET_KD_GAS = 'BJU' in the WHERE clause turns it into an inner join because no row which doesn't have b.KET_KD_GAS = 'BJU' can be accepted by the query, thus requiring the row in RKAP_MASTER_KODE to exist. If you really want the join to be optional then move the b.KET_KD_GAS predicate into the join clause - but if you do that, what's the point of joining RKAP_MASTER_KODE anyways since you use it to optionally limit the results, which isn't really a "limit".

Besides that, though, Oracle doesn't allow joins in an UPDATE statement. The workaround is to use a sub-SELECT to limit the updates to the appropriate rows:

UPDATE WFINANCE.RKAP_PROYEKSI a
  SET a.STATUS = 'Waiting Approval',
      a.T_ID = 'TR1234'
  WHERE a.TAHUN = '2018' AND
        a.KODE_ANGGARAN IN (SELECT b.ID_KODE
                              FROM WFINANCE.RKAP_MASTER_KODE b
                              WHERE b.KET_KD_GAS = 'BJU')

If you really don't care if there's a matching row in RKAP_MASTER_KODE, just drop that part of the UPDATE:

UPDATE WFINANCE.RKAP_PROYEKSI a
  SET a.STATUS = 'Waiting Approval',
      a.T_ID = 'TR1234'
  WHERE a.TAHUN = '2018'

score 0 · Accepted Answer · answered Sep 11 '19 at 03:16

use this instead.

UPDATE 
    (SELECT a.STATUS , a.T_ID 
      FROM WFINANCE.RKAP_PROYEKSI a
      LEFT JOIN WFINANCE.RKAP_MASTER_KODE b ON b.ID_KODE = a.KODE_ANGGARAN
      WHERE 
        a.TAHUN = '2018' AND b.KET_KD_GAS = 'BJU') t1
SET 
    t1.STATUS = 'Waiting Approval',
    t1.T_ID = 'TR1234'

Left Join On Update ORA-00971: missing SET keyword SQL

2 Answers2