| Update statement with INNER JOIN [message #689077] |
Fri, 15 September 2023 05:45  |
 |
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'
Regards
Suji
|
|
|
|
|
|
| Re: Update statement with INNER JOIN [message #689079 is a reply to message #689078] |
Fri, 15 September 2023 08:51  |
 |
mathguy
Messages: 77
Registered: January 2023
|
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 |
Solomon Yakobson
Messages: 3246
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'
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
