| Author |
Topic |
|
NeilG
Aged Yak Warrior
530 Posts |
 Posted - 2009-05-08 : 07:39:24
|
Hi guys,
i'm a little stuck here at the moment so hope someone can help,
I've a table which im changing a code which was 1,2 or 9 to now read 6 however because because some were subscribed to 1,2, and 9 and then other 1,2 and other 9,1 etc its proving hard removing duplicates before updating.
I also can't use a Select distinct into a #temp table, remove rows and then reinsert method, as all the rows all have a uniqueidentifier column on the end which then makes them all distinct.
Hope someone can help with this
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-05-08 : 07:58:43
|
It is not easy to understand...
Can you give table structure, sample data and wanted result please?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2009-05-08 : 08:04:56
|
This is the table
UserID CODE Date RowGuid
3196229 6 2006-02-15 00:00:00.000 963805A4-DB5B-4379-9901-F4DEDC6F0EC3
3196229 6 2006-02-15 00:00:00.000 53660646-4B15-484C-8575-BCC8F573016C
3196229 6 2006-02-15 00:00:00.000 FF9F16AF-714D-4300-BB78-DF0FD84ED0EB
But i want to have just one of these rows in the table and the others removed, however remember this is in SQL 2000 so there is no OVER function etc
Hope this is more clear |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-05-08 : 08:11:39
|
Why is CODE already 6?
Is this right?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2009-05-08 : 08:14:45
|
I can change that back but that is what happen during the update to change them to 6 but it could read
UserID CODE Date RowGuid
3196229 1 2006-02-15 00:00:00.000 963805A4-DB5B-4379-9901-F4DEDC6F0EC3
3196229 2 2006-02-15 00:00:00.000 53660646-4B15-484C-8575-BCC8F573016C
3196229 9 2006-02-15 00:00:00.000 FF9F16AF-714D-4300-BB78-DF0FD84ED0EB |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-05-08 : 08:20:37
|
Ok now I understand.
Update is already done.
Rows are distinct except in RowGuid.
I will try a solution now...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-05-08 : 08:26:51
|
[code]
delete d
from table d
join (select UserId,CODE,[Date],MAX(RowGuid) as RowGuid
from table group by UserId,CODE,[Date]) t
on d.UserID=t.UserID AND
d.CODE=t.CODE AND
d.[Date]=t.[Date] AND
d.RowGuid <> t.RowGuid
[/code]
Please have a test, but first replace 'table' by your tablename.
Webfred
Edit: initial situation is: CODE is already DISTINCT
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2009-05-08 : 08:33:27
|
| Tested this but it falls down that you can't have a max() on the rowguid |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-05-08 : 08:43:10
|
Oh sorry!
Please replace
MAX(RowGuid) as RowGuid
with
MAX(convert(varchar(255),RowGuid)) as RowGuid
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2009-05-08 : 09:02:08
|
Massive thanks - I was playing around with the max rowguid but never thought of the converting first
Its greate sometimes to have a fresh set of eye to help, hope to help you sometime. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-05-08 : 09:07:18
|
My pleasure! 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
atulchakarvarti
Starting Member
2 Posts |
Posted - 2009-05-09 : 09:38:58
|
hi all,
i have a prob in sql... i have some data in excel file and i embedded that data into sql2000 to remove duplicacy!! my data contains name, telephoneno. ! now the prob is i want to remove duplicate phn nos. and names. but in many rows phn nos. are same but names are different.and i want to del the row where phn no. is duplicate.so plz help me to remove duplicacy in sql |
|
|
|
|
Removing duplicates without removing from table