My table data looks like this
My poorly attempted SQL is this…
MERGE INTO PRINT target USING ( select ID,PDF_FILE from PRINT where date(PRINTED) = '2022-01-06' and PDF_FILE is not null ) sause ON (target.ID = sause.ID) WHEN MATCHED THEN UPDATE SET target.PDF_FILE = sause.PDF_FILE
It is updating all rows in the table. I do not want this.
How can I make it ONLY update the 1 latest PRINTED row which has an empty PDF_FILE ?
Advertisement
Answer
The idea is to enumerate target rows and update only the 1-st one.
MERGE INTO ( SELECT ID, PDF_FILE, ROW_NUMBER () OVER (PARTITION BY ID ORDER BY PRINTED DESC) AS RN_ FROM PRINT WHERE -- Below is an appropriate condition for -- the target rows to distinguish them from the source row -- PDF_FILE IS NULL date (PRINTED) <> '2022-01-06' ) target USING ( select ID,PDF_FILE from PRINT where date (PRINTED) = '2022-01-06' and PDF_FILE is not null ) sause ON target.ID = sause.ID AND target.RN_ = 1 WHEN MATCHED THEN UPDATE SET target.PDF_FILE = sause.PDF_FILE