The situation I´m trying to solve is that I have an Oracle DB table (11g) which has duplicates (a row is considered duplicate if the DB already contains an exact combination of Product_Name,Product_Code and Product_ID the rows at the point when they are created are assigned a “VALID_FROM”date.
Now I want to retrieve these duplicates rows and assign a VALID_UNTIL (sysdate) to the row whichever has the older date for “VALID_FROM”. (and in the process I want to check that the “VALID_UNTIL” would be null so I wouldn´t be overwriting anything.
I tried the below code which retrieves the duplicates but how do I indicate that insert sysdate to the row where the date is the earliest...and also do I need to group them as well, because I suspect that If I simply tell it to choose the earliest date and assing the date it will do so acrosse the whole select, whereas I want it to be assigned within each set of duplicates.
SELECT A.* FROM DATA_TABLE A
HAVING Count(*) >1
ON a. PRODUCT_NAME = b. PRODUCT_NAME
AND a. PRODUCT_CODE = b. PRODUCT_CODE
AND a. PRODUCT_ID = b. PRODUCT_ID
ORDER BY A.VALID_FROM