Deleting a row(s)

Question

Deleting a row(s)

sql_2005_fan

SSCommitted

Points: 1871
More actions
January 19, 2017 at 4:00 pm

#339513

I have the following data set.
Sequence CurrentStatusId PreviousStatusId
1 1 1
2 1 1
3 1 9
4 9 1
5 10 9
The previousStatusId of the sequence 3 is not same as the currentStatusID of the sequence 2. So I need to delete the Sequence 3 from this data set and I need to get the following result. There is a chance that multiple records can be like this and I need to delete all of them.
Sequence CurrentStatusId PreviousStatusId
1 1 1
2 1 1
4 9 1
5 10 9
I am using a cursor to this but the performance is really bad.
Can any one provide a query to do this?
Thanks in advance.

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

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

Phil Parkin SSC Guru Points: 246723 More actions · Answer 1

sql_2005_fan - Thursday, January 19, 2017 4:00 PM
I have the following data set.
Sequence CurrentStatusId PreviousStatusId
1 1 1
2 1 1
3 1 9
4 9 1
5 10 9
The previousStatusId of the sequence 3 is not same as the currentStatusID of the sequence 2. So I need to delete the Sequence 3 from this data set and I need to get the following result. There is a chance that multiple records can be like this and I need to delete all of them.
Sequence CurrentStatusId PreviousStatusId
1 1 1
2 1 1
4 9 1
5 10 9
I am using a cursor to this but the performance is really bad.
Can any one provide a query to do this?
Thanks in advance.

Here's one:

IF OBJECT_ID('tempdb..#tbl', 'U') IS NOT NULL DROP TABLE #tbl;

CREATE TABLE #tbl ( Seq INT PRIMARY KEY CLUSTERED, CurrentStatusId INT, PreviousStatusId INT );

INSERT #tbl ( Seq, CurrentStatusId, PreviousStatusId ) VALUES (1, 1, 1), (2, 1, 1), (3, 1, 9), (4, 9, 1), (5, 10, 9);

SELECT * FROM #tbl t;

WITH LaggedData AS (SELECT t.Seq, t.PreviousStatusId, TruePrevStatId = LAG(t.CurrentStatusId, 1, 1) OVER (ORDER BY t.Seq) FROM #tbl t ) DELETE t FROM #tbl t JOIN LaggedData ld ON t.Seq = ld.Seq WHERE ld.PreviousStatusId <> ld.TruePrevStatId;

SELECT * FROM #tbl t;

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

drew.allen SSC Guru Points: 76948 More actions · Answer 2

I think that we need a better data set if we're going to give a good answer. Your current data set only has one record that will be deleted. We need a record set that is more complicated. Specifically, we need records that are affected by the deletion of previous records. Specifically, record 4 has the same results whether it uses record 3 (before the deletion) or record 2 (after the deletion), but we need examples where the results change.

When setting up the sample data, you should use a script to create the table followed by insert statements (like Phil did in his solution).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

sql_2005_fan SSCommitted Points: 1871 More actions · Answer 3

Sure. I will do in the future.

Thanks for the help. It worked really well.

sql_2005_fan SSCommitted Points: 1871 More actions · Answer 4

Can anyone help me to have the below query with not having the clause 'LaggedData'. I am not able to use this on SQL server 2000 instance.

Thanks.

IF OBJECT_ID('tempdb..#tbl', 'U') IS NOT NULL
DROP TABLE #tbl;

CREATE TABLE #tbl
(
Seq INT PRIMARY KEY CLUSTERED,
CurrentStatusId INT,
PreviousStatusId INT
);

INSERT #tbl
(
Seq,
CurrentStatusId,
PreviousStatusId
)
VALUES
(1, 1, 1),
(2, 1, 1),
(3, 1, 9),
(4, 9, 1),
(5, 10, 9);

SELECT *
FROM #tbl t;

WITH LaggedData
AS (SELECT t.Seq,
t.PreviousStatusId,
TruePrevStatId = LAG(t.CurrentStatusId, 1, 1) OVER (ORDER BY t.Seq)
FROM #tbl t
)
DELETE t
FROM #tbl t
JOIN LaggedData ld
ON t.Seq = ld.Seq
WHERE ld.PreviousStatusId <> ld.TruePrevStatId;

SELECT *
FROM #tbl t;

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

sql_2005_fan - Monday, February 6, 2017 4:27 AM
Can anyone help me to have the below query with not having the clause 'LaggedData'. I am not able to use this on SQL server 2000 instance.

The LAG function first appeared in SQL Server 2012, and the Common Table Expression (CTE) in SQL Server 2005, so there's two things you have to change. It'll only work if you don't have any gaps in the values in the Seq column. If you do, it's much more complicated.

DELETE t FROM #tbl t JOIN ( -- subquery instead of CTE SELECT t1.Seq, t1.PreviousStatusId, TruePrevStatId = COALESCE(t2.PreviousStatusId,1) FROM #tbl t1 LEFT JOIN #tbl t2 ON t1.Seq = t2.Seq + 1 -- self-join instead of LAG function ) ld on t.Seq = ld.Seq WHERE ld.PreviousStatusId <> ld.TruePrevStatId;

John

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 6

Now that you've got a couple of answers, I have to ask... who is fixing the original code that caused this problem to begin with?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)