[SQL] Update Query updates all records instead of records in where Clause (DB2)

Please help me the fix the below query, it is supposed to update the duplicate records and not all records by grouping the fields MEMBID, FSTNAM, LSTNAM, PRVID.

UPDATE RL4CLT.Duplicaterecords
SET ERRSTS = 'B' -- Set to 'B' for duplicates
WHERE TRNID = 650500 -- Specify the TRNID for filtering
AND (MEMBID, FSTNAM, LSTNAM, PRVID) IN (
SELECT MEMBID, FSTNAM, LSTNAM, PRVID
FROM RL4CLT.Duplicaterecords
WHERE TRNID = 650500 -- Ensure you are checking within the same TRNID
GROUP BY MEMBID, FSTNAM, LSTNAM, PRVID
HAVING COUNT(*) > 1 -- Only include those with duplicates
);


I tried using Row_num and also using one more subquery, didn't work. It needs to update only the records with matching values in above specified fields.

Continue reading...