Link to home
Start Free TrialLog in
Avatar of brgdotnet
brgdotnetFlag for United States of America

asked on

I need help with my update statement

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
ASKER CERTIFIED SOLUTION
Avatar of chaau
chaau
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of brgdotnet

ASKER

Thanks, I actually figured it out, but your solution ROCKS