CODEWITHSUNDEEP
sqloracle-database
Boerejef
Boerejef

Reputation: 53

SQL command not properly ended in update statement with multiple sets

I have following Oracle update statement 

 UPDATE details
    SET details.ISO_BNK_TX_CODE_PRTRY_CODE = details.TX_TYP_CODE,
        details.ISO_BNK_TX_CODE_PRTRY_ISSUER = 'BAI'
    FROM AS_ACCT_STAT_DET details
    JOIN AS_ACCT_STAT statements ON details.ACCT_STAT_ID = statements.ID
    JOIN MSG_FDEF fdef ON statements.W_R_SOURCE_FORMAT_ID = fdef.ID
    WHERE fdef.CODE = 'BAI2'
    /

I made the original script in SQL Server. But I thought this code would work for both. But I receive following error message:

Error at Command Line : 4 Column : 1 Error report - SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause:
*Action:

I think something is missing between the last set and the FROM statement.

Anyone?

Upvotes: 0

Views: 918

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

Oracle doesn't allow a join in an update statement, without a subquery anyway. You may be able to use an updatable view here, but you don't need to as you aren't getting the new values from one of the joined tables.

So it looks like you can just do:

UPDATE details
SET details.ISO_BNK_TX_CODE_PRTRY_CODE = details.TX_TYP_CODE,
    details.ISO_BNK_TX_CODE_PRTRY_ISSUER = 'BAI'
WHERE EXISTS (
    SELECT null
    FROM AS_ACCT_STAT statements
    JOIN MSG_FDEF fdef ON statements.W_R_SOURCE_FORMAT_ID = fdef.ID
    WHERE statements.ID = details.ACCT_STAT_ID
    AND fdef.CODE = 'BAI2'
)
/

That will only update rows where there is matching data in the other two tables, from the exists() condition; that subquery is correlated with the table being updated via its where clause.

Upvotes: 0

jonju
jonju

Reputation: 2736

Try this query

UPDATED

UPDATE (
        Select 
             details.ISO_BNK_TX_CODE_PRTRY_CODE ,details.TX_TYP_CODE, 
             details.ISO_BNK_TX_CODE_PRTRY_ISSUER
        FROM
             AS_ACCT_STAT_DET details   
        INNER JOIN 
             AS_ACCT_STAT statements ON details.ACCT_STAT_ID = statements.ID
        INNER JOIN
             MSG_FDEF fdef ON statements.W_R_SOURCE_FORMAT_ID = fdef.ID
        WHERE 
             fdef.CODE = 'BAI2'        
) d 
SET 
  d.ISO_BNK_TX_CODE_PRTRY_CODE = d.TX_TYP_CODE,
  d.ISO_BNK_TX_CODE_PRTRY_ISSUER = 'BAI'

Upvotes: 0

Related Questions