Help in writing Delete statement

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

General SQL Server Forums

New to SQL Server Programming

Help in writing Delete statement

Author

Topic

adeelusman
Starting Member

3 Posts

Posted - 2013-07-30 : 03:08:16

Hello all
i have problem to discuss regarding delete statement. Due to the bad design of application i have added some duplicate values in table. i have fixed the issue for new insert but how to delete my old bad id. here is my problem. i have table having three column,
ID1 ID2 ID3
101 2 3
102 2 3
103 2 4
104 3 4

in my case first two record are duplicate i want to keep first one and delete the other. how can i do this

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-30 : 03:32:35

DELETE f
FROM (
select row_number() OVER (Partition by id2, id3 order by id1 desc) as rn from dbo.Table1
) AS f
WHERE rn > 1

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-30 : 03:37:08

i want to keep first one and delete the other.
Either of last suggestion or this


DELETE f
FROM (
select row_number() OVER (Partition by id2, id3 order by id1 desc) as rn from dbo.Table1
) AS f
WHERE rn > 1

depending on whether you want to retain earlier id or latest one

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-30 : 03:47:26

[code]
DELETE f
FROM dbo.Table1 f
WHERE NOT EXISTS (SELECT 1
FROM dbo.Table1
WHERE id2 = f.id2
AND id3 = f.id3
HAVING MIN(id1) = f.id1
)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

adeelusman
Starting Member

3 Posts

Posted - 2013-07-30 : 03:53:05

[code]ID ID2 ID3

06c41c7e-c4ee-4939-83c1-e75eafd035f0 31cbb642-d0e8-4b20-88d0-d67a80d02eb3 1de19af4-48a7-4cc6-aba5-bed33a29c495

1f9fa05f-a567-46dd-a81c-ccd715403b54 da1e84c5-f485-4b25-9df1-a2c3ac09adbf ad77cebe-c4fe-4486-aed0-e01b1337db62

4d729934-d484-46ab-916a-3cc89ff12acb 67a7f0ac-342f-4a6a-8a03-c21d526bad2f 6019d269-dd45-47d8-ae83-e584655a491f

54b4240a-5355-472b-a819-75498c1698ce 49306459-0841-4cfb-813c-6d9ecd52e548 9c69d222-c068-4854-bd8a-cfd5a200959b

664bc281-a783-4309-bda9-333a35325893 67a7f0ac-342f-4a6a-8a03-c21d526bad2f 6019d269-dd45-47d8-ae83-e584655a491f

75d5046e-820c-4859-b626-8f7f23df8480 5a9d91b4-3fff-4353-9101-036f46555e46 61446e0e-44b8-4a7f-ba5f-8dcad5364478

885ae21b-a4bd-4ae5-a22f-daa2f4256732 da1e84c5-f485-4b25-9df1-a2c3ac09adbf 9b1fc67a-9c56-4249-a1b6-021e4bfc0391

930b76d5-ea4a-4dca-b7c8-f842e8ca7f8e ae9d00be-8487-4a45-9f2c-1a7ccdaa43b4 b44ad438-7d71-4fc4-af6c-4a0816ce1997

9512e5c5-a60b-4c12-9050-915b48079d6b ef332537-4bc7-4367-80db-f28719f0232c 86ff88a9-7a10-451b-ba9c-34d289a77c06

b0c4d339-d73a-4888-9a3f-da08be1eb755 ae9d00be-8487-4a45-9f2c-1a7ccdaa43b4 b44ad438-7d71-4fc4-af6c-4a0816ce1997

b10f96e0-b32b-4147-b801-583e8b197233 67a7f0ac-342f-4a6a-8a03-c21d526bad2f 6019d269-dd45-47d8-ae83-e584655a491f

ba0774e4-d832-4099-a275-ebbae0185225 da1e84c5-f485-4b25-9df1-a2c3ac09adbf ad77cebe-c4fe-4486-aed0-e01b1337db62

eb9b3975-56c9-4037-85f5-42a4827231f3 5a9d91b4-3fff-4353-9101-036f46555e46 61446e0e-44b8-4a7f-ba5f-8dcad5364478

eff4d5d4-e330-486c-b181-5614ebfb384a 652f5f6d-37be-46c4-a213-54f431a88c80 60b78892-cbac-4aa6-9075-e2ec7c5fda00
[/code]

ID1 is unique whereas ID2 and ID3 can be replicate. Look at record 3 and 5. Both ID2 and ID3 are same whereas ID1 for both are different. My problem is that i want to delete any of one record to fix the issue how can it possible? any suggestion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-30 : 04:01:18

whats the problem with given suggestions? did they not work for you?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

adeelusman
Starting Member

3 Posts

Posted - 2013-07-30 : 04:03:43

No,the give solution delete the five record whereas it should delete only one either 3 or 5

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-30 : 04:13:28

No, you have more duplicates than that!
See following code, you are bound to delete 5 rows.

DECLARE	@Sample TABLE
	(
		ID1 UNIQUEIDENTIFIER NOT NULL,
		ID2 UNIQUEIDENTIFIER NOT NULL,
		ID3 UNIQUEIDENTIFIER NOT NULL
	);

INSERT	@Sample
	(
		ID1,
		ID2,
		ID3
	)
VALUES	('06c41c7e-c4ee-4939-83c1-e75eafd035f0', '31cbb642-d0e8-4b20-88d0-d67a80d02eb3', '1de19af4-48a7-4cc6-aba5-bed33a29c495'),
	('1f9fa05f-a567-46dd-a81c-ccd715403b54', 'da1e84c5-f485-4b25-9df1-a2c3ac09adbf', 'ad77cebe-c4fe-4486-aed0-e01b1337db62'),
	('4d729934-d484-46ab-916a-3cc89ff12acb', '67a7f0ac-342f-4a6a-8a03-c21d526bad2f', '6019d269-dd45-47d8-ae83-e584655a491f'),
	('54b4240a-5355-472b-a819-75498c1698ce', '49306459-0841-4cfb-813c-6d9ecd52e548', '9c69d222-c068-4854-bd8a-cfd5a200959b'),
	('664bc281-a783-4309-bda9-333a35325893', '67a7f0ac-342f-4a6a-8a03-c21d526bad2f', '6019d269-dd45-47d8-ae83-e584655a491f'),
	('75d5046e-820c-4859-b626-8f7f23df8480', '5a9d91b4-3fff-4353-9101-036f46555e46', '61446e0e-44b8-4a7f-ba5f-8dcad5364478'),
	('885ae21b-a4bd-4ae5-a22f-daa2f4256732', 'da1e84c5-f485-4b25-9df1-a2c3ac09adbf', '9b1fc67a-9c56-4249-a1b6-021e4bfc0391'),
	('930b76d5-ea4a-4dca-b7c8-f842e8ca7f8e', 'ae9d00be-8487-4a45-9f2c-1a7ccdaa43b4', 'b44ad438-7d71-4fc4-af6c-4a0816ce1997'),
	('9512e5c5-a60b-4c12-9050-915b48079d6b', 'ef332537-4bc7-4367-80db-f28719f0232c', '86ff88a9-7a10-451b-ba9c-34d289a77c06'),
	('b0c4d339-d73a-4888-9a3f-da08be1eb755', 'ae9d00be-8487-4a45-9f2c-1a7ccdaa43b4', 'b44ad438-7d71-4fc4-af6c-4a0816ce1997'),
	('b10f96e0-b32b-4147-b801-583e8b197233', '67a7f0ac-342f-4a6a-8a03-c21d526bad2f', '6019d269-dd45-47d8-ae83-e584655a491f'),
	('ba0774e4-d832-4099-a275-ebbae0185225', 'da1e84c5-f485-4b25-9df1-a2c3ac09adbf', 'ad77cebe-c4fe-4486-aed0-e01b1337db62'),
	('eb9b3975-56c9-4037-85f5-42a4827231f3', '5a9d91b4-3fff-4353-9101-036f46555e46', '61446e0e-44b8-4a7f-ba5f-8dcad5364478'),
	('eff4d5d4-e330-486c-b181-5614ebfb384a', '652f5f6d-37be-46c4-a213-54f431a88c80', '60b78892-cbac-4aa6-9075-e2ec7c5fda00');

-- Before
SELECT	*,
	SIGN(COUNT(*) OVER (PARTITION BY ID2, ID3) - 1) AS IsDuplicate
FROM	@Sample;

DELETE	f
FROM	(
		SELECT	ROW_NUMBER() OVER (PARTITION BY ID2, ID3 ORDER BY ID1 DESC) AS rn
		FROM	@Sample
	) AS f
WHERE	rn > 1;

-- After
SELECT	*
FROM	@Sample;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-30 : 04:17:07

[code]

declare @t table
(
ID1 uniqueidentifier,
ID2 uniqueidentifier,
ID3 uniqueidentifier
)

insert @t
values

('06c41c7e-c4ee-4939-83c1-e75eafd035f0','31cbb642-d0e8-4b20-88d0-d67a80d02eb3','1de19af4-48a7-4cc6-aba5-bed33a29c495'),
('1f9fa05f-a567-46dd-a81c-ccd715403b54','da1e84c5-f485-4b25-9df1-a2c3ac09adbf','ad77cebe-c4fe-4486-aed0-e01b1337db62'),
('4d729934-d484-46ab-916a-3cc89ff12acb','67a7f0ac-342f-4a6a-8a03-c21d526bad2f','6019d269-dd45-47d8-ae83-e584655a491f'),
('54b4240a-5355-472b-a819-75498c1698ce','49306459-0841-4cfb-813c-6d9ecd52e548','9c69d222-c068-4854-bd8a-cfd5a200959b'),
('664bc281-a783-4309-bda9-333a35325893','67a7f0ac-342f-4a6a-8a03-c21d526bad2f','6019d269-dd45-47d8-ae83-e584655a491f'),
('75d5046e-820c-4859-b626-8f7f23df8480','5a9d91b4-3fff-4353-9101-036f46555e46','61446e0e-44b8-4a7f-ba5f-8dcad5364478'),
('885ae21b-a4bd-4ae5-a22f-daa2f4256732','da1e84c5-f485-4b25-9df1-a2c3ac09adbf','9b1fc67a-9c56-4249-a1b6-021e4bfc0391'),
('930b76d5-ea4a-4dca-b7c8-f842e8ca7f8e','ae9d00be-8487-4a45-9f2c-1a7ccdaa43b4','b44ad438-7d71-4fc4-af6c-4a0816ce1997'),
('9512e5c5-a60b-4c12-9050-915b48079d6b','ef332537-4bc7-4367-80db-f28719f0232c','86ff88a9-7a10-451b-ba9c-34d289a77c06'),
('b0c4d339-d73a-4888-9a3f-da08be1eb755','ae9d00be-8487-4a45-9f2c-1a7ccdaa43b4','b44ad438-7d71-4fc4-af6c-4a0816ce1997'),
('b10f96e0-b32b-4147-b801-583e8b197233','67a7f0ac-342f-4a6a-8a03-c21d526bad2f','6019d269-dd45-47d8-ae83-e584655a491f'),
('ba0774e4-d832-4099-a275-ebbae0185225','da1e84c5-f485-4b25-9df1-a2c3ac09adbf','ad77cebe-c4fe-4486-aed0-e01b1337db62'),
('eb9b3975-56c9-4037-85f5-42a4827231f3','5a9d91b4-3fff-4353-9101-036f46555e46','61446e0e-44b8-4a7f-ba5f-8dcad5364478'),
('eff4d5d4-e330-486c-b181-5614ebfb384a','652f5f6d-37be-46c4-a213-54f431a88c80','60b78892-cbac-4aa6-9075-e2ec7c5fda00')

select * from @t

delete t
from
(
select ROW_NUMBER() OVER (PARTITION BY id2,id3 ORDER BY id1) AS Seq,*
FROM @t
)t
WHERE Seq>1

select * from @t

output
---------------------------------------------------------------------
before deletion

ID1 ID2 ID3
------------------------------------------------------------------------------------------------------------------------
06C41C7E-C4EE-4939-83C1-E75EAFD035F0 31CBB642-D0E8-4B20-88D0-D67A80D02EB3 1DE19AF4-48A7-4CC6-ABA5-BED33A29C495
1F9FA05F-A567-46DD-A81C-CCD715403B54 DA1E84C5-F485-4B25-9DF1-A2C3AC09ADBF AD77CEBE-C4FE-4486-AED0-E01B1337DB62
4D729934-D484-46AB-916A-3CC89FF12ACB 67A7F0AC-342F-4A6A-8A03-C21D526BAD2F 6019D269-DD45-47D8-AE83-E584655A491F
54B4240A-5355-472B-A819-75498C1698CE 49306459-0841-4CFB-813C-6D9ECD52E548 9C69D222-C068-4854-BD8A-CFD5A200959B
664BC281-A783-4309-BDA9-333A35325893 67A7F0AC-342F-4A6A-8A03-C21D526BAD2F 6019D269-DD45-47D8-AE83-E584655A491F
75D5046E-820C-4859-B626-8F7F23DF8480 5A9D91B4-3FFF-4353-9101-036F46555E46 61446E0E-44B8-4A7F-BA5F-8DCAD5364478
885AE21B-A4BD-4AE5-A22F-DAA2F4256732 DA1E84C5-F485-4B25-9DF1-A2C3AC09ADBF 9B1FC67A-9C56-4249-A1B6-021E4BFC0391
930B76D5-EA4A-4DCA-B7C8-F842E8CA7F8E AE9D00BE-8487-4A45-9F2C-1A7CCDAA43B4 B44AD438-7D71-4FC4-AF6C-4A0816CE1997
9512E5C5-A60B-4C12-9050-915B48079D6B EF332537-4BC7-4367-80DB-F28719F0232C 86FF88A9-7A10-451B-BA9C-34D289A77C06
B0C4D339-D73A-4888-9A3F-DA08BE1EB755 AE9D00BE-8487-4A45-9F2C-1A7CCDAA43B4 B44AD438-7D71-4FC4-AF6C-4A0816CE1997
B10F96E0-B32B-4147-B801-583E8B197233 67A7F0AC-342F-4A6A-8A03-C21D526BAD2F 6019D269-DD45-47D8-AE83-E584655A491F
BA0774E4-D832-4099-A275-EBBAE0185225 DA1E84C5-F485-4B25-9DF1-A2C3AC09ADBF AD77CEBE-C4FE-4486-AED0-E01B1337DB62
EB9B3975-56C9-4037-85F5-42A4827231F3 5A9D91B4-3FFF-4353-9101-036F46555E46 61446E0E-44B8-4A7F-BA5F-8DCAD5364478
EFF4D5D4-E330-486C-B181-5614EBFB384A 652F5F6D-37BE-46C4-A213-54F431A88C80 60B78892-CBAC-4AA6-9075-E2EC7C5FDA00

after deletion

ID1 ID2 ID3
-------------------------------------------------------------------------------------------------------------------------
06C41C7E-C4EE-4939-83C1-E75EAFD035F0 31CBB642-D0E8-4B20-88D0-D67A80D02EB3 1DE19AF4-48A7-4CC6-ABA5-BED33A29C495
1F9FA05F-A567-46DD-A81C-CCD715403B54 DA1E84C5-F485-4B25-9DF1-A2C3AC09ADBF AD77CEBE-C4FE-4486-AED0-E01B1337DB62
54B4240A-5355-472B-A819-75498C1698CE 49306459-0841-4CFB-813C-6D9ECD52E548 9C69D222-C068-4854-BD8A-CFD5A200959B
664BC281-A783-4309-BDA9-333A35325893 67A7F0AC-342F-4A6A-8A03-C21D526BAD2F 6019D269-DD45-47D8-AE83-E584655A491F
885AE21B-A4BD-4AE5-A22F-DAA2F4256732 DA1E84C5-F485-4B25-9DF1-A2C3AC09ADBF 9B1FC67A-9C56-4249-A1B6-021E4BFC0391
9512E5C5-A60B-4C12-9050-915B48079D6B EF332537-4BC7-4367-80DB-F28719F0232C 86FF88A9-7A10-451B-BA9C-34D289A77C06
B0C4D339-D73A-4888-9A3F-DA08BE1EB755 AE9D00BE-8487-4A45-9F2C-1A7CCDAA43B4 B44AD438-7D71-4FC4-AF6C-4A0816CE1997
EB9B3975-56C9-4037-85F5-42A4827231F3 5A9D91B4-3FFF-4353-9101-036F46555E46 61446E0E-44B8-4A7F-BA5F-8DCAD5364478
EFF4D5D4-E330-486C-B181-5614EBFB384A 652F5F6D-37BE-46C4-A213-54F431A88C80 60B78892-CBAC-4AA6-9075-E2EC7C5FDA00

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources