April 6, 2005 at 7:06 am
Hi,
I don't know if this is possible or not, but I really hope it is...
The goal is to find out if there are duplicate IDs being imported. I won't go into the detail of the messed up data, but suffice it to say that I know there are duplicate IDs, but I don't want to hard code those IDs because we'd like to be able to use this again.
I figured I could just count the IDs, and leave out the ones that were not equal to 1. So, I would like to do something to the effect of:
SELECT Count(No), No
FROM Request
WHERE COUNT(No) <> 1
GROUP BY No
WITHOUT the WHERE clause, the results would be something like:
1, 345
1, 346
2, 347
3, 348
1, 349
etc...
I know you can't use the Count in the WHERE... so is there a way to accomplish this? I'd like to incorporate this statement into an UPDATE statement so that it will update field1 in the table to 0 if the count equals something other than 1. (So where No is 347 and 348, field1 would be set to 0, while 345, 346,and 348 would remain 1.) Then when I do the import, I'll just pull all the records where the value of field1 is not 0.
I'd like to keep this as simple as possible, too. It's ultimately going into a SQL task in a DTS.
Thanks in advance for any help!!
April 6, 2005 at 7:22 am
SELECT Count(No) as Total, No
FROM dbo.Request
GROUP BY No
HAVING COUNT(No) 1
April 6, 2005 at 7:27 am
Awesome! Thanks... I knew it was something simple!!!
April 7, 2005 at 3:22 pm
Yeah, when using aggregate function such as COUNT, MAX and SUM,, always change the WHERE clause into HAVING
Ezz Khayyat
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy