Hi all,
Not sure if I am doing this right. My SQL is identifying the records that I want to delete correctly, when I do manual QC, but I am not sure how to delete the identified records out of the Table.
The Table that I want to delete out of has many columns, but when a condition is met on the 2 columns that I am searching on, I want to delete the rest of the columns, regardless of what they are.
When the combination of the two columns CUST_ID and MIN(STAMP_DATE) exists, then delete that row, or maybe I am not quite doing this right. Basically there are 2 rows (duplicates for each cust_id) and I want to delete the rows with the oldest Date, that's why I am using MIN(DATE) Date_field and keep the Max(Date) for the rest of the fields.
Thanks for help ahead of time.
DELETE FROM Table_A
WHERE EXISTS
(
SELECT CUST_ID, MIN(STAMP_DATE) as MIN_DATE
FROM
(
SELECT CUST_ID, STAMP_DATE
FROM
(
SELECT T1.*, COUNT(*) OVER ( PARTITION BY CUST_ID ) X
FROM Table_A T1
WHERE SALES_PERSON is null
)
WHERE X > 1
ORDER BY CUST_ID)
Group by CUST_ID
);