SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help in writing Delete statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

adeelusman
Starting Member

3 Posts

Posted - 07/30/2013 :  03:08:16  Show Profile  Reply with Quote

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

Sweden
30277 Posts

Posted - 07/30/2013 :  03:32:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/30/2013 :  03:37:08  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/30/2013 :  03:47:26  Show Profile  Reply with Quote

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
                 )


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

adeelusman
Starting Member

3 Posts

Posted - 07/30/2013 :  03:53:05  Show Profile  Reply with Quote
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


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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/30/2013 :  04:01:18  Show Profile  Reply with Quote
whats the problem with given suggestions? did they not work for you?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

adeelusman
Starting Member

3 Posts

Posted - 07/30/2013 :  04:03:43  Show Profile  Reply with Quote
No,the give solution delete the five record whereas it should delete only one either 3 or 5
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 07/30/2013 :  04:13:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/30/2013 :  04:17:07  Show Profile  Reply with Quote


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




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000