Deleting duplicates -- sql server programming

Enric
8/3/2004 11:45:01 PM

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblProducts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblProducts]
GO

CREATE TABLE [dbo].[tblProducts] (
[intIFPWhsProductsID] [int] IDENTITY (1, 1) NOT NULL ,
[sinPeriodID] [smallint] NOT NULL ,
[sinWhsCodeID] [smallint] NOT NULL ,
[strWhsProdCode] [char] (20) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[strDesc] [varchar] (100) COLLATE Modern_Spanish_CI_AS NULL

) ON [PRIMARY]
GO

insert into tblProducts(sinPeriodId,sinWhsCodeId,strWhsProdCode,strDesc)
values(26,69,3456,'item 1 sma 1')

insert into tblProducts(sinPeriodId,sinWhsCodeId,strWhsProdCode,strDesc)
values(26,69,3456,'item 1 sma 100 g')
insert into tblProducts(sinPeriodId,sinWhsCodeId,strWhsProdCode,strDesc)
values(26,69,3456,'item 1 sma 100 g prod')
insert into tblProducts(sinPeriodId,sinWhsCodeId,strWhsProdCode,strDesc)
values(26,69,3456,'item 1')

Thanks for that,

[quoted text, click to view]

Enric
8/4/2004 4:43:02 AM

Thanks for your support Roji but your example it doesn't work.
just delete a row (there are 8,000 rows in that table)

[quoted text, click to view]

Steve
8/4/2004 9:34:24 AM

That'll be a *whoosh* then.

In news:1DC6A72A-5595-4C0B-80A7-A5FC7B7DB585@microsoft.com,
Enric <Enric@discussions.microsoft.com> said:

[snip SQL details]

[quoted text, click to view]

--
Steve

Roji. P. Thomas
8/4/2004 12:04:57 PM

Who designed this table?
Is he available for consulting?

--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com

[quoted text, click to view]
positions (I suppose through Len() function) that one must be remains in the
aforementioned table.
[quoted text, click to view]

Roji. P. Thomas
8/4/2004 12:33:53 PM

Sorry If I sound arrogant.
Here is the solution.

CREATE TABLE #Temp([DESC] VARCHAR(100), PRODUCTID int)

INSERT INTO #Temp VALUES('item 1 200 mg', 33456)
INSERT INTO #Temp VALUES('item 1 2', 33456)
INSERT INTO #Temp VALUES('item 1 200 mg box 2', 33456)
INSERT INTO #Temp VALUES('item 1 200 mg box 2 ind 1', 33456)

INSERT INTO #Temp VALUES('item 2 200 mg', 33457)
INSERT INTO #Temp VALUES('item 2 2', 33457)
INSERT INTO #Temp VALUES('item 2 200 mg box 2', 33457)
INSERT INTO #Temp VALUES('item 2 200 mg box blah blah', 33457)

SELECT * FROM #Temp

DELETE FROM #Temp
WHERE [DESC] NOT IN(SELECT
[DESC] FROM #Temp WHERE LEN([DESC]) = ANY(
SELECT Max(LEN([DESC])) FROM #Temp
GROUP BY PRODUCTID))

SELECT * FROM #Temp

DROP TABLE #Temp

--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com

[quoted text, click to view]
positions (I suppose through Len() function) that one must be remains in the
aforementioned table.
[quoted text, click to view]

Hugo Kornelis
8/4/2004 1:58:09 PM

[quoted text, click to view]

Hi Enric,

Based on your narrative, DDL and sample data:

delete
from tblProducts
where exists
(select *
from tblProducts AS dup
where dup.strWhsProdCode = tblProducts.strWhsProdCode
and len(dup.strDesc) > len(tblProducts.strDesc))

Enclose in a transaction and rollback or commit after checking the
results.

Note that this may leave duplicates if the have the same length in
strDesc.

Best, Hugo
--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Deleting duplicates