June 14, 2005 at 11:31 am
Hiya i want to do a simple self-join on a table and delete the duplicates.. if i use
select a.lastname, a.firstname, a.id from theatre a
inner join theatre b
on a.lastname = b.lastname
where a.lastname is not null and a.firstname is not null
and a.lastname <> '' and a.firstname <> ''
and a.firstname = b.firstname
and a.id <> b.id
then it brings back both original and duplicate whereas i only want to select one to delete so i can do somet6hing like this
delete from theatre where id in
(
a.id from theatre a
inner join theatre b
on a.lastname = b.lastname
where a.lastname is not null and a.firstname is not null
and a.lastname <> '' and a.firstname <> ''
and a.firstname = b.firstname
and a.id <> b.id)
any thoughts?
thanks!
June 14, 2005 at 1:32 pm
SET NOCOUNT ON
DECLARE @theatre TABLE
(
[ID] INT IDENTITY,
LastName VARCHAR(25),
FirstName VARCHAR(25)
)
INSERT INTO @theatre (LastName, FirstName) VALUES ('L1111', 'F1111')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L2222', 'F2222')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L3333', 'F3333')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L4444', 'F4444')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L5555', 'F5555')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L6666', 'F6666')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L7777', 'F7777')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L8888', 'F8888')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L9999', 'F9999')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L0000', 'F0000')
--Duplicates
INSERT INTO @theatre (LastName, FirstName) VALUES ('L5555', 'F5555')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L7777', 'F7777')
-- List Duplicates
SELECT LastName, FirstName, MAX([ID])
FROM
@theatre
GROUP BY LastName, FirstName
HAVING COUNT(*) > 1
--DELETE Duplicates
DELETE @theatre
WHERE
[ID] IN
(
SELECT MAX([ID])
FROM
@theatre
GROUP BY LastName, FirstName
HAVING COUNT(*) > 1)
SELECT * FROM @theatre
Regards,
gova
June 14, 2005 at 2:33 pm
This will work for ANY No of duplicates and will keep the first row with the MIN(ID)
SET NOCOUNT ON
DECLARE @theatre TABLE
(
[ID] INT IDENTITY,
LastName VARCHAR(25),
FirstName VARCHAR(25)
)
INSERT INTO @theatre (LastName, FirstName) VALUES ('L1111', 'F1111')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L2222', 'F2222')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L3333', 'F3333')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L4444', 'F4444')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L5555', 'F5555')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L6666', 'F6666')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L7777', 'F7777')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L8888', 'F8888')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L9999', 'F9999')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L0000', 'F0000')
--Duplicates
INSERT INTO @theatre (LastName, FirstName) VALUES ('L5555', 'F5555')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L7777', 'F7777')
INSERT INTO @theatre (LastName, FirstName) VALUES ('L5555', 'F5555')
-- List Duplicates
SELECT LastName, FirstName, Count(*) As Duplicates
FROM
@theatre
GROUP BY LastName, FirstName
HAVING COUNT(*) > 1
--DELETE Duplicates
DELETE A
FROM @theatre A INNER JOIN
(SELECT MIN(ID) ID,LastName,FirstName
FROM @theatre
GROUP BY LastName, FirstName
HAVING COUNT(*) > 1) B
ON A.LastName=B.LastName and A.FirstNAme=B.FirstName
WHERE A.ID>B.ID
SELECT * FROM @theatre
Vasc
June 15, 2005 at 3:29 am
ah didnt think to use count.. very good thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply