Window functions in an update statement in Oracle SQL

Question

I am using Oracle SQL DB 19c. Tried the following Oracle SQL scripts but none of them worked to add a column to my table with window expression.

The first script runs to an error message: "SQL command not properly ended":

UPDATE TRANSACTION_HISTORY SET TRANSACTION_HISTORY.ID = X.RN
    FROM
    (SELECT SEQUENCE,
    ROW_NUMBER() OVER (PARTITION BY TRANSACTION_DATE, TRANSACTION_PARTNER ORDER BY TRANSACTION_DATE, TRANSACTION_PARTNER ASC) AS RN
    FROM TRANSACTION_HISTORY
    ) AS X
    WHERE TRANSACTION_HISTORY.SEQUENCE = X.SEQUENCE

The second one runs to an error message: "missing ON keyword" but ON is there:

merge into TH USING
    (SELECT TRANSACTION_DATE, TRANSACTION_PARTNER, SEQUENCE,
    ROW_NUMBER() OVER (PARTITION BY TRANSACTION_DATE, TRANSACTION_PARTNER ORDER BY TRANSACTION_DATE, TRANSACTION_PARTNER ASC) AS RN
    FROM TRANSACTION_HISTORY TH
    ) X
ON X.SEQUENCE = TH.SEQUENCE
WHEN MATCHED THEN UPDATE 
SET TH.ID = X.RN

Can someone please help?

question from:https://stackoverflow.com/questions/65917187/window-functions-in-an-update-statement-in-oracle-sql

Answer 1

You need parenthesis after ON as follows:

merge into TH USING
    (SELECT SEQUENCE, 
            ROW_NUMBER() OVER (PARTITION BY TRANSACTION_DATE, TRANSACTION_PARTNER 
                              ORDER BY TRANSACTION_DATE, TRANSACTION_PARTNER ASC) AS RN
    FROM TRANSACTION_HISTORY TH
    ) X
ON (X.SEQUENCE = TH.SEQUENCE) -- you need to use parenthesis
WHEN MATCHED THEN UPDATE 
SET TH.ID = X.RN