You have to be very carefully when doing such things. There are some risks/things you should take care of:
- What should happen with NULL values?
- What should be done when there is no entry for a certain date having a MDE transaction?
- What should be done when there are multiple MDE transactions at the same date?
- Do you just need to count or even count distinct?
- Maybe further points I did not think about
So, overall, this is quite risky and you should be really sure about what you're doing. But this is a matter of your setup that you need to check on your own.
What I can tell you is just a general way do do these things and then you have to check what exactly you will do in your case.
In order to simply select all product numbers having multiple dates, you can do this:
SELECT productnumber
FROM yourtable
GROUP BY productnumber
HAVING COUNT(effectivedate) > 1;
As I wrote above, you might add a DISTINCT if you don't want to count the same date twice: ...COUNT(DISTINCT effectivedate).
To find the product number and the effectivedate for transactiontype MDE, you can do this query:
SELECT productnumber, effectivedate FROM yourtable
WHERE transactiontype = 'MDE'
So you can JOIN these two results to see the product number and effectivedate for those product numbers only that occur with multiple dates and have a MDE transaction:
SELECT t1.productnumber, t2.effectivedate FROM
(SELECT productnumber
FROM yourtable
GROUP BY productnumber
HAVING COUNT(effectivedate) > 1) t1
JOIN
(SELECT productnumber, effectivedate FROM yourtable
WHERE transactiontype = 'MDE') t2 ON t1.productnumber=t2.productnumber
If you are sure you really want to update your rows, you can use this join and do an update like this:
UPDATE yourtable SET effectivedate = x.newdate FROM
(SELECT t1.productnumber AS refnumber, t2.effectivedate AS newdate FROM
(SELECT productnumber
FROM yourtable
GROUP BY productnumber
HAVING COUNT(effectivedate) > 1) t1
JOIN
(SELECT productnumber, effectivedate FROM yourtable
WHERE transactiontype = 'MDE') t2 ON t1.productnumber=t2.productnumber) x
WHERE productnumber = x.refnumber;
Please see here a working example with sample data: db<>fiddle
As already said, I don't recommend such risky data modifications, but prefer to make sure to set correct data within the appilcation which fills this table.
