RE: Proper Syntax to update column when a duplicate is found 3

[EBOUGHEY]

I looked through the posts and saw lots of queries to pull duplicate information, but how would you update a column to tag the dupes?

I want to do something like the below although I know this particular one doesn't work....

UPDATE [63805-06]
SET QUERY_ID = 'DUPES'
from [63805-06] A1, [63805-06] A2
WHERE A1.LASTNAME = A2.LASTNAME
AND A1.ADDRESS1 = A2.ADDRESS1
AND A1.ZIP = A2.ZIP
HAVING
count(*) > 1

 

[Catadmin]

Someone might have a better way to do this (mine is kinda brute force), but first, you need to make sure the column you're using to set to 'Dupes' isn't a clustered index or primary key of somesort. I only mention that because of the name you gave it, Query_ID.

Here's what I do.

Update [63805-06]
set Query_ID = 'Duplicate'
where A1.Lastname in (Select Lastname from [63805-06])
and A1.Address1 in (Select Address1 from [63805-06])
and A1.Zip in (Select Zip from [63805-06])
and <primarykey> < (Select <primarykey> from [63805-06])

You'll have to play with it some on a test db. I've only actually used it to delete duplicate lines with 2 parameters, one of them a date_time datatype. Let me know if it works or not.

If someone has a better one to run, let me know also. I'm still learning the code part of this stuff. @=)



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[EBOUGHEY]

No primary key in the file.

 

[Pattycake245]

Are you looking to tag all the records matching each other or just the extras? In other words, if there are 5 dupes do you want to update the QUERY_ID field to 'Duplicate for all of them or 4 of them. If it is for 4 of them, this would be a lot easier of you had some kind of unique column for each row, then you could use this:

Update [63805-06]
set Query_ID = 'Duplicate'
from [63805-06] a
where identity_column not in(select min(identity_column) from [63805-06] WHERE LASTNAME = a.LASTNAME
AND ADDRESS1 = a.ADDRESS1
AND ZIP = a.ZIP)

I'm not sure there is a way to do it without a unique column with accurate results. I'll think about that one.

Tim

 

[donutman]

If you want to tag them all then you can do it this way:

[Blue]UPDATE[/Blue] A1
   [Blue]SET[/Blue] QUERY_ID [Gray]=[/Gray] [red]'DUPES'[/red]
   [Blue]FROM[/Blue] [63805-06] A1 [Blue]INNER[/Blue] [Gray]JOIN[/Gray] 
      [Gray]([/Gray][Blue]SELECT[/Blue] LastName[Gray],[/Gray] Address1[Gray],[/Gray] Zip [Blue]FROM[/Blue] [63805-06] 
          [Blue]GROUP[/Blue] [Blue]BY[/Blue] LastName[Gray],[/Gray] Address1[Gray],[/Gray] Zip
          [Blue]HAVING[/Blue] [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray][Gray]*[/Gray][Gray])[/Gray][Gray]>[/Gray]1[Gray])[/Gray] A2
   [Blue]ON[/Blue] [Gray]([/Gray]A1.LASTNAME [Gray]=[/Gray] A2.LASTNAME [Gray]AND[/Gray]
       A1.ADDRESS1 [Gray]=[/Gray] A2.ADDRESS1 [Gray]AND[/Gray]
       A1.ZIP [Gray]=[/Gray] A2.ZIP[Gray])[/Gray]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[EBOUGHEY]

Pattycake,

That worked perfectly! I created a uniqueid column and waalaaaa!

Thanks so much for your help. you get a star!

Elena

 

[Pattycake245]

Karl, that was pretty much the solution I came up with on the way home last night. Star for you

Tim

 

[EBOUGHEY]

Also donutman's suggestion is perfect for when I am doing a suppression and need to delete all matches.... star for you too!

I have 2 answers in one question. You guys are AWESOME!

Elena