How to Delete Duplicates Rows (Special case)

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

SQL Server Development (2000)

How to Delete Duplicates Rows (Special case)

Author

Topic

Clages1
Yak Posting Veteran

69 Posts

Posted - 2008-10-09 : 09:49:34

Hi, I know how to delete duplicate Rows,
But in this case, i am not succeed

Table Name "Alterna"
Alt_dep, Alt_produto, Alt_cliente are Keys
in this case i want to delete
003 123888 33
005 123888 33
007 123777 33
because they already exists as Alt_dep = '001'

Alt_dep Alt_Produto Alt_cliente Alt_NewProduto Alt_newName
001 123456 33 XYTRRR
001 123777 33 ZZZZ
001 123888 33 VVVVVV

003 123888 33 VVVVVV
005 123888 33 VVVVVV
007 123777 33 ZZZZ

001 123456 55 XYTRRR
001 123777 55 ZZZZ
001 123888 55 VVVVVV

Tks for any help
Clages

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-09 : 10:10:57

First of all,

Is there some reason you don't have a unique key on this table?

you should make a surrogate key at the very least.

If you had one this would be trivially easy.

Can you change schema?

-------------
Charlie

Clages1
Yak Posting Veteran

69 Posts

Posted - 2008-10-09 : 10:25:52

Sorry
Alt_dep + Alt_produto + Alt_cliente are UNIQUE KEY
In fact i want to change this table to only
Alt_produto + Alt_cliente to be a unique key
but first i need to delete the duplicate rows as i said earlier
Tks
Clages

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-09 : 10:38:31

declare @Alterna table
(
alt_dep varchar(5),
alt_produto int,
alt_cliente int,
Alt_NewProduto char(20),
Alt_newName char(20)
)
;

insert into @Alterna (alt_dep,alt_produto,alt_cliente,Alt_NewProduto)
select '001', 123456, 33, 'XYTRRR'
UNION ALL
SELECT '001', 123777, 33, 'ZZZZ'
UNION ALL
SELECT '001', 123888, 33, 'VVVVVV'
UNION ALL
SELECT '003', 123888, 33, 'VVVVVV'
UNION ALL
SELECT '005', 123888, 33, 'VVVVVV'
UNION ALL
SELECT '007', 123777, 33, 'ZZZZ'
UNION ALL
SELECT '001', 123456, 55, 'XYTRRR'
UNION ALL
SELECT '001', 123777, 55, 'ZZZZ'
UNION ALL
SELECT '001', 123888, 55, 'VVVVVV'
;
select * from @alterna;

delete from @alterna where alt_dep+cast(alt_produto as varchar)+cast(alt_cliente as varchar) not in (
select (select top 1 alt_dep+cast(alt_produto as varchar)+cast(alt_cliente as varchar) from @alterna a where cast(a.alt_produto as varchar)+cast(a.alt_cliente as varchar)+cast(a.Alt_NewProduto as varchar)+isnull(a.alt_newname,'')
= cast(b.alt_produto as varchar)+cast(b.alt_cliente as varchar)+cast(b.Alt_NewProduto as varchar)+isnull(b.alt_newname,'')
order by alt_dep asc)
from @alterna b);

select * from @alterna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-09 : 10:41:24

[code]DELETE w
FROM Alterna AS w
LEFT JOIN (
SELECT altProduto,
altCliente,
MIN(altDep) AS dep
FROM Alterna
GROUP BY altProduto,
altCliente
) AS s ON s.altProduto = w.altProduto
AND s.altCliente = w.altCliente
AND s.dep = w.altDep
WHERE s.altProduto IS NULL[/code]

E 12°55'05.63"
N 56°04'39.26"

Clages1
Yak Posting Veteran

69 Posts

Posted - 2008-10-10 : 15:30:10

hanbingl, thanks
your solution works fine for me

Peso your solutions i dont know why doesnt work
in my case

tks all

Clages

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources