Update all duplicates in one query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update all duplicates in one query

Author

Topic

BabySteps
Starting Member

5 Posts

Posted - 2005-06-01 : 02:21:59

I've been looking around and this is one of the more helpful and knowledgable forums. I plan to be a long time member.

I am trying to create a query that only needs to be run ONCE that updates all duplicates so the database ignores them. This is done by setting the "removed date" column to be "todays date", instead of a past date or null.

The following query will update 1 duplicate. I'm stumped on making it update all duplicates without needing to re-run it.

Update a
Set Removeddate = getutcdate()
from eternals a
join (Select max(EternalID) as EternalID
FROM eternals
WHERE RemovedDate > GetUTCDate()
and eternalid > 0
GROUP BY Cloud
HAVING Count(EternalID) > 1) b ON a.EternalID = b.EternalID

I have no need to insert data into a temp table. I just need to update all duplicates found with "todays date" in one shot. This will be added as another entry to an existing stored procedure that does other tasks. In reading other threads the idea of using a loop came up, but it's unclear to me how to do that. Sorry if it's obvious, I'm still learning. :)

Matt

BabySteps
Starting Member

5 Posts

Posted - 2005-06-01 : 02:40:24

Got to get to bed, but partially answered a question. Unless I'm really out of it or too tired, loop won't work. It would be useful if I was saying "run this script" but not from within a t-sql script. So I guess I'm even more "lost" about how to do this. Any help, or pointers in the right direction would be HIGHLY appreciated.

Matt

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-06-01 : 03:10:39

youi cannot have a group by and a where clause in one query

i'm not even sure how you have this working for you but HTH

Update a
Set Removeddate = getutcdate()
from eternals a
join (Select max(EternalID) as EternalID,cloud
FROM eternals
group by cloud
having count(*)>1) b ON a.EternalID = b.EternalID
WHERE a.RemovedDate > GetUTCDate()
and a.eternalid > 0

--------------------
keeping it simple...

BabySteps
Starting Member

5 Posts

Posted - 2005-06-01 : 09:45:04

Thanks for the reply. :) I'll compare the two later today.

Still have to add more to this to update so all duplicates and not just the MAX one is updated in one running of the query.

Matt

BabySteps
Starting Member

5 Posts

Posted - 2005-06-02 : 00:06:45

First, the query you had to "fix" what I had didn't work properly. Not sure what to say there.

Second, I was able to get my query finished and working to update all duplicates in one shot.

Thanks,

Matt

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-06-02 : 14:36:21

care to show us the "working" query?

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-02 : 14:49:38

quote:
Originally posted by jen

youi cannot have a group by and a where clause in one query

Huh?

Tara

BabySteps
Starting Member

5 Posts

Posted - 2005-06-03 : 02:19:35

Sorry Jen, I looked into your reply Jen more, and I'm not sure where you think that.

Final Query:
Update a
Set Removeddate = getutcdate()
FROM Eternals a
JOIN (SELECT Cloud, min(EternalID) as minEternalID
FROM Eternals
WHERE RemovedDate > getutcdate()
AND EternalID > 0
GROUP BY Cloud
HAVING Count(EternalID) > 1) dupli on dupli.Cloud = a.Cloud
and a.EternalID != dupli.minEternalID

Matt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources