SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table yourtable (productnumber varchar(100), EffectiveDate date,
Transactiontype varchar(100), SourceSystemName varchar(100));
insert into yourtable values ('47-CAO-01','01-25-2021','MDE','DOM_SUBM_01');
insert into yourtable values ('47-CAO-01','01-27-2021','Drtbd','INT_NA_01');
insert into yourtable values ('47-CAO-02','01-28-2021','Drtbd','INT_NA_01');
insert into yourtable values ('47-CAO-02','01-29-2021','MDE','INT_NA_01');
insert into yourtable values ('47-CAO-03','01-26-2021','MDE','INT_NA_01');
insert into yourtable values ('47-CAO-04','01-27-2021','Test','INT_NA_01');
insert into yourtable values ('47-CAO-04','01-27-2021','Test','INT_NA_01');
insert into yourtable values ('47-CAO-04','01-31-2021','MDE','INT_NA_01');
8 rows affected

SELECT productnumber, COUNT(EffectiveDate) AS countDates
FROM yourtable
GROUP BY productnumber;


productnumber
countDates


47-CAO-01
2

47-CAO-02
2

47-CAO-03
1

47-CAO-04
3


SELECT * FROM yourtable;


productnumber
EffectiveDate
Transactiontype
SourceSystemName


47-CAO-01
2021-01-25
MDE
DOM_SUBM_01

47-CAO-01
2021-01-27
Drtbd
INT_NA_01

47-CAO-02
2021-01-28
Drtbd
INT_NA_01

47-CAO-02
2021-01-29
MDE
INT_NA_01

47-CAO-03
2021-01-26
MDE
INT_NA_01

47-CAO-04
2021-01-27
Test
INT_NA_01

47-CAO-04
2021-01-27
Test
INT_NA_01

47-CAO-04
2021-01-31
MDE
INT_NA_01


SELECT productnumber
FROM yourtable
GROUP BY productnumber
HAVING COUNT(effectivedate) > 1;


productnumber


47-CAO-01

47-CAO-02

47-CAO-04


SELECT productnumber, effectivedate FROM yourtable 
WHERE transactiontype = 'MDE'


productnumber
effectivedate


47-CAO-01
2021-01-25

47-CAO-02
2021-01-29

47-CAO-03
2021-01-26

47-CAO-04
2021-01-31


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


productnumber
effectivedate


47-CAO-01
2021-01-25

47-CAO-02
2021-01-29

47-CAO-04
2021-01-31


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;
7 rows affected

SELECT * FROM yourtable;


productnumber
EffectiveDate
Transactiontype
SourceSystemName


47-CAO-01
2021-01-25
MDE
DOM_SUBM_01

47-CAO-01
2021-01-25
Drtbd
INT_NA_01

47-CAO-02
2021-01-29
Drtbd
INT_NA_01

47-CAO-02
2021-01-29
MDE
INT_NA_01

47-CAO-03
2021-01-26
MDE
INT_NA_01

47-CAO-04
2021-01-31
Test
INT_NA_01

47-CAO-04
2021-01-31
Test
INT_NA_01

47-CAO-04
2021-01-31
MDE
INT_NA_01

productnumber	EffectiveDate	Transactiontype	SourceSystemName
47-CAO-01	2021-01-25	MDE	DOM_SUBM_01
47-CAO-01	2021-01-27	Drtbd	INT_NA_01
47-CAO-02	2021-01-28	Drtbd	INT_NA_01
47-CAO-02	2021-01-29	MDE	INT_NA_01
47-CAO-03	2021-01-26	MDE	INT_NA_01
47-CAO-04	2021-01-27	Test	INT_NA_01
47-CAO-04	2021-01-27	Test	INT_NA_01
47-CAO-04	2021-01-31	MDE	INT_NA_01