I have a table named Woop with some sample data as shown below.
The table has duplicate Code values. In this case it has two
records with a code value of IAEE.
I want to keep only one record which contains a code of IAEE.
I need to keep the IAEE record which has the earliest DateEntered date.
For the other records which contain a code of IAEE I want to change their codes to
pound signs ####
So here is the table data before
Code DateEntered
IXXX 2014-12-07 08:20:58.407
IAEE 2014-12-07 08:35:18.853
IAEE 2014-12-07 08:36:28.763
Here is how the table should look after I run the update statement below
Code DateEntered
IXXX 2014-12-07 08:20:58.407
IAEE 2014-12-07 08:35:18.853
#### 2014-12-07 08:36:28.763
update t
set Code = '####'
from Woop t
where t.DateEntered > ( select min(x.DateEntered) from Woop x ) AND Code = 'IAAE' -- AND SUBSTRING(t.Code,1,1) = 'A'
END
My update statement is broken, because it changes both records to have a code value of ####. What is wrong with my update statement?
Here is what is ultimately produced, and which is wrong.
Code DateEntered
IXXX 2014-12-07 08:20:58.407
#### 2014-12-07 08:35:18.853
#### 2014-12-07 08:36:28.763