SQL Syntax help for a DELETE statement

Question

SQL Syntax help for a DELETE statement

mw_sql_developer

SSCoach

Points: 19445
More actions
July 13, 2015 at 2:58 pm

#318977

create table abc( lab_id varchar(10), lab_time datetime, lab_name varchar(100) )
INSERT INTO abc( '10001' , '2015-07-13 14:01:023' , 'K' );
INSERT INTO abc( '10001' , '2015-07-13 14:01:023' , 'K' );
INSERT INTO abc( '10001' , '2015-07-13 14:01:023' , 'K' );
INSERT INTO abc( '10001' , '2015-07-13 14:01:023' , 'K' );
INSERT INTO abc( '10002' , '2015-07-12 14:01:024' , 'Na' );
INSERT INTO abc( '10002' , '2015-07-12 14:01:024' , 'Na' );
INSERT INTO abc( '10002' , '2015-07-12 14:01:024' , 'Na' );
INSERT INTO abc( '10002' , '2015-07-12 14:01:024' , 'Na' );
Question, How do we eliminate duplicates ?
After deleting there should be only 2 rows left ?
SQL help please ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

mw112009 (7/13/2015)
create table abc( lab_id varchar(10), lab_time datetime, lab_name varchar(100) )
INSERT INTO abc( '10001' , '2015-07-13 14:01:023' , 'K' );
INSERT INTO abc( '10001' , '2015-07-13 14:01:023' , 'K' );
INSERT INTO abc( '10001' , '2015-07-13 14:01:023' , 'K' );
INSERT INTO abc( '10001' , '2015-07-13 14:01:023' , 'K' );
INSERT INTO abc( '10002' , '2015-07-12 14:01:024' , 'Na' );
INSERT INTO abc( '10002' , '2015-07-12 14:01:024' , 'Na' );
INSERT INTO abc( '10002' , '2015-07-12 14:01:024' , 'Na' );
INSERT INTO abc( '10002' , '2015-07-12 14:01:024' , 'Na' );
Question, How do we eliminate duplicates ?
After deleting there should be only 2 rows left ?
SQL help please ?

Quick example

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.abc') IS NOT NULL DROP TABLE dbo.abc;

create table dbo.abc( lab_id varchar(10), lab_time datetime, lab_name varchar(100) )

INSERT INTO dbo.abc( lab_id , lab_time , lab_name )

VALUES ( '10001' , '2015-07-13 14:01:023' , 'K' )

,( '10001' , '2015-07-13 14:01:023' , 'K' )

,( '10002' , '2015-07-12 14:01:024' , 'Na' )

;

;WITH DEDUPE AS

(

SELECT

ROW_NUMBER() OVER

(

PARTITION BY A.lab_id

,A.lab_time

,A.lab_name

ORDER BY (SELECT NULL)

) AS RID

FROM dbo.abc A

)

DELETE

FROM DEDUPE

WHERE RID > 1;

SELECT

*

FROM dbo.abc;

Results

lab_id lab_time lab_name

---------- ----------------------- -----------

10001 2015-07-13 14:01:23.000 K

10002 2015-07-12 14:01:24.000 Na

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

Are you sure ? I am confused on the following

DELETE

FROM DEDUPE

WHERE RID > 1;

Should it be --->

DELETE

FROM AbC

WHERE ???

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

mw112009 (7/13/2015)
Are you sure ? I am confused on the following
DELETE
FROM DEDUPE
WHERE RID > 1;
Should it be --->
DELETE
FROM AbC
WHERE ???

Eirikur's code is correct. What you are doing is deleting the data from the table ABC based on the data returned in the CTE DEDUPE. This allows you to identify the duplicate data (RID > 1).

sgmunson SSC Guru Points: 110640 More actions · Answer 4

mw112009 (7/13/2015)
Are you sure ? I am confused on the following
DELETE
FROM DEDUPE
WHERE RID > 1;
Should it be --->
DELETE
FROM AbC
WHERE ???

When a CTE exists, it acts very much like a view, and you can INSERT INTO, SELECT FROM, UPDATE, and DELETE FROM it, just as if it were a view, provided you meet the right conditions, and you'll have to look those up, as I don't recall the exact rules. Most of the time, a single table being involved and for most queries, it's gonna be good to go, and the operation performed flows through to the base table.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

sgmunson (7/14/2015)
mw112009 (7/13/2015)
Are you sure ? I am confused on the following
DELETE
FROM DEDUPE
WHERE RID > 1;
Should it be --->
DELETE
FROM AbC
WHERE ???
When a CTE exists, it acts very much like a view, and you can INSERT INTO, SELECT FROM, UPDATE, and DELETE FROM it, just as if it were a view, provided you meet the right conditions, and you'll have to look those up, as I don't recall the exact rules. Most of the time, a single table being involved and for most queries, it's gonna be good to go, and the operation performed flows through to the base table.

Here's the reference from MSDN on modifying data through views. The same rules apply to CTEs. https://msdn.microsoft.com/en-us/library/ms180800.aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2