• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

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
0
brgdotnet
Asked:
brgdotnet
1 Solution
 
chaauCommented:
If you want to update just for IAAE then you need to add it to the subquery:
update t
    set Code = '####'  
    from Woop t
    where t.DateEntered > ( select min(x.DateEntered) from Woop x where x.Code=t.Code)  AND Code = 'IAAE' -- AND SUBSTRING(t.Code,1,1) = 'A'
END

Open in new window

I have provided a generic solution requiring entering the  Code = 'IAAE' once. You could also do this like this:
update t
    set Code = '####'  
    from Woop t
    where t.DateEntered > ( select min(x.DateEntered) from Woop x where x.Code='IAAE')  AND Code = 'IAAE' -- AND SUBSTRING(t.Code,1,1) = 'A'
END

Open in new window

However, if you have many duplicating records you can do a generic query that will take care of all them:
WITH q as(
SELECT *,
ROW_NUMBER() OVER(Partition BY Code Order BY DateEntered) rn
FROM Woop
Where Code <> '####')
Update q Set code = '####' WHERE rn > 1

Open in new window

0
 
brgdotnetAuthor Commented:
Thanks, I actually figured it out, but your solution ROCKS
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now