Koray Kırdinli

Yazılım ve İş Yaşamı Hakkında Paylaşımlar

T-SQL’de duplike kayıtları silmek (With kullanımı)

CREATE TABLE tablo (Col1 INT, Col2 INT)
GO

INSERT INTO tablo VALUES(1,1),(1,1), –duplicate
(1,1), –duplicate
(1,2),
(1,2), –duplicate
(1,3),
(1,4)
GO

SELECT * FROM tablo
GO

WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM tablo
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

SELECT * FROM tablo
GO

DROP TABLE tablo

Buradaki CTE ,  Common Table Expression demektir ve bize rekülsive sorguları basitleştirmek için bir yöntem sunar.
WITH CTE name ( columns)
AS ( CTE definition )
SELECT|INERT|UPDATE|DELETE… FROM… CTE name
DEtaylı bilgiyi aşağıdaki linklerden edinebilirsiniz.
http://www.csharpnedir.com/articles/read/?id=477&title=SQL%20Server%202005%20:%20Recursive%28%C3%96z-yinelemeli%29%20Sorgular%20ve%20CTE

http://msdn.microsoft.com/en-us/library/ms175972.aspx

February 22, 2012 - Posted by | MSSql | , , ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s