Home » SQL & PL/SQL » SQL & PL/SQL » Update statement with INNER JOIN (oracle)
Update statement with INNER JOIN [message #689077] Fri, 15 September 2023 05:45 Go to next message
suji6281
Messages: 131
Registered: September 2014
Senior Member
Hi Team,

I was trying to prepare an UPDATE statement with INNER JOIN. I was getting "missing SET keyword" error while executing the update sql.
I was trying to update PROJECT_TBL with the data available in VOUCHER_TBL. below is the code I have written, please help me with the correct sql.

UPDATE PROJECT_TBL B 
INNER JOIN VOUCHER_TBL A
ON A.BU = B.BU
AND A.VOUCHER_ID = B.VOUCHER_ID
AND A.PROJECT_ID = B.PROJECT_ID
AND A.VOUCHER_LINE = B.VOUCHER_LINE
SET B.JOURANL_ID = A.JOURNAL_ID,
B.JOURNAL_DATE = A.JOURNAL_DATE,
B.JOURNAL_LINE = A.JOURNAL_LINE
WHERE B.JOURNAL_ID = 'JRNL6713'

Thank you for your suggestion on this.

Regards
Suji
Re: Update statement with INNER JOIN [message #689078 is a reply to message #689077] Fri, 15 September 2023 05:50 Go to previous messageGo to next message
John Watson
Messages: 8916
Registered: January 2010
Location: Global Village
Senior Member
I think you need to use MERGE, not UPDATE.
Re: Update statement with INNER JOIN [message #689079 is a reply to message #689078] Fri, 15 September 2023 08:51 Go to previous messageGo to next message
mathguy
Messages: 105
Registered: January 2023
Senior Member
Contrary to a widely held belief, you can update through a join in Oracle db. But there are two conditions.

First, the lookup table must be "key preserved". In your case, it should be true that (BU, VOUCHER_ID, PROJECT_ID, VOUCHER_LINE) is a (composite) unique key in table VOUCHER_TBL, and that must be known to the db (you must have a primary key or a unique constraint on that combination of columns, and that must be declared as such in the db.)

If this condition is not satisfied, then the task itself is illogical; if the same row in the "updated" table matches more than one row in the "lookup" table, which row in the "lookup" table should be used for the update? Oracle wants to know about this at parse time; other db products may trust the query writer, and throw an error only at runtime (and only if duplicates are found), but Oracle is strict in this regard.

If the combination of those columns does not have duplicates, but that is not known through a constraint (or a unique index), you will have to use MERGE, as Jon said.

If the combination is unique and there is a constraint that says so, then the second condition is that the correct syntax must be used. What you got was a syntax error, which was checked first; but there is no point in fixing it, if the first condition (about the composite uniqueness) is not satisfied.

So - before showing the correct syntax - please clarify about the uniqueness. Is that combination of columns unique? And if so, is that maintained through a constraint, or a unique index?
Re: Update statement with INNER JOIN [message #689080 is a reply to message #689079] Fri, 15 September 2023 11:10 Go to previous message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
I'll assume SET B.JOURANL_ID = A.JOURNAL_ID is just a typo and it should be SET B.JOURNAL_ID = A.JOURNAL_ID. Anyway:

UPDATE PROJECT_TBL B
   SET (
        B.JOURNAL_ID,
        B.JOURNAL_DATE,
        B.JOURNAL_LINE
       ) = (
            SELECT  A.JOURNAL_ID
                    A.JOURNAL_DATE,
                    A.JOURNAL_LINE
              FROM  VOUCHER_TBL A
              WHERE A.BU = B.BU
                AND A.VOUCHER_ID = B.VOUCHER_ID
                AND A.PROJECT_ID = B.PROJECT_ID
                AND A.VOUCHER_LINE = B.VOUCHER_LINE
           )
  WHERE B.JOURNAL_ID = 'JRNL6713'
/
SY.
Previous Topic: Consuming Rest API from PL/SQL using UTL_HTTP - body not recognized
Next Topic: how to get employee in and out time for generated query based on first in and last out
Goto Forum:
  


Current Time: Tue Feb 27 21:41:19 CST 2024