Cursor Help

Question

Post reply

Cursor Help

rocky_498

SSCertifiable

Points: 6532
More actions
January 10, 2014 at 5:24 pm

#302995

Thanks, this topic is Delete

Viewing 10 posts - 1 through 9 (of 9 total)

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Cursor is a bad word around here... careful.

That said, I don't think you need a cursor at all.

CREATE Table #TableA

(

ID int IDENTITY(1,1),

SID INT,

FNAME varchar(50),

LNAMEvarchar(50),

DOB date,

CITYvarchar(50),

IsActive bit

);

GO

INSERT INTO #TableA

VALUES ('245','Smith','John','1/10/1998','Los Angles','0'); -- will be made Active=1.

INSERT INTO #TableA

VALUES ('298','Smith','John','1/10/1998','Los Angles','0');

INSERT INTO #TableA

VALUES ('987','Smith','John','1/10/1998','Los Angles','0');

INSERT INTO #TableA

VALUES ('451','Collins','Albert','3/1/2003','Los Angles','0'); -- will be made Active=1.

UPDATE #TableA

SET IsActive = 1

WHERE [SID] IN

(SELECT MIN([SID]) AS GoodID

FROM #TableA

GROUP BY FName

, LName

, DOB

, City);

results:

IDSIDFNAMELNAMEDOBCITYIsActive

1245SmithJohn1998-01-10Los Angles1 <-- first one of group, so set Active =1

2298SmithJohn1998-01-10Los Angles0

3987SmithJohn1998-01-10Los Angles0

4451CollinsAlbert2003-03-01Los Angles1 <-- first one of group, so set Active =1

right?

rocky_498 SSCertifiable Points: 6532 More actions · Answer 2

Thank You for your reply,

That was a sample data that i post, User can select anything from front end to merge, it could be max sid or min sid don't know. I agree with you Cursor is very bad, sometime if you don't have no choice then. I will check later to optimize my SP. Could you please help me to find the problem, why cursor is running infinite loop?

Thank You.

UPDATE #TableA

SET IsActive = 1

WHERE [SID] IN

(SELECT MIN([SID]) AS GoodID

FROM #TableA

GROUP BY FName

, LName

, DOB

, City);

pietlinden SSC Guru Points: 63503 More actions · Answer 3

So in theory they could merge two records that don't match? Could you post an example? Explain why you can't do it with either a CTE or a simple update statement. What are the rules on how the stuff gets merged?

rocky_498 SSCertifiable Points: 6532 More actions · Answer 4

Here is simple e.g

Here is my sample data

IDSIDFNAMELNAMEDOBCITYIsActive

2245SmithJohn1998-01-10Los Angles0

3298SmithJohn1998-01-10Los Angles0

4987SmithJohn1998-01-10Los Angles0

If I select in sp, SID = 245,Fname=Smith,Lname = John and DOB = 1998-01-10 is right, rest all the data in the data with Fname=Smith,Lname = John and DOB = 1998-01-10 to inactive, it could be 4/5/ or any numbers, so that's why i am using Cursor inside SP,

Exec Merge '245','smith','john','1998-01-10'

IDSIDFNAMELNAMEDOBCITYIsActive

2245SmithJohn1998-01-10Los Angles0

3298SmithJohn1998-01-10Los Angles1

4987SmithJohn1998-01-10Los Angles1

So end result should be like this from sample data,

make sense?

pietlinden SSC Guru Points: 63503 More actions · Answer 5

So sue me.

--Mark the records we want to delete, just to be sure we don't do anything terrible.

UPDATE #TableA

SET IsActive = 1

WHERE [SID] IN

(SELECT MIN([SID]) AS GoodID

FROM #TableA

GROUP BY FName

, LName

, DOB

, City);

-- now that the records are marked, delete those.

DELETE

FROM #TableA

WHERE IsActive = 0;

Race you... see which wins between that and using a cursor...

pietlinden SSC Guru Points: 63503 More actions · Answer 6

Okay, here's the DELETE as a CTE, which is probably the easiest way to do it... (once you read the crib notes)

; WITH CTE_DupeAddr(DupeID, ID, Cid, FName, LName, DOB, City) AS

(

SELECT

ROW_NUMBER() OVER (PARTITION BY FName, LName, DOB, City ORDER BY FName, LName) AS DupeID

, ID

, [SID]

, FName

, LName

, DOB

, City

FROM #TableA

)

/*

-- show what will be deleted

SELECT * FROM CTE_DupeAddr

WHERE DupeID>1;

*/

DELETE

FROM CTE_DupeAddr

WHERE DupeID>1;

(Gotta love Rick Morelan's simple examples!)

comment out the SELECT/DELETE statement, depending on which you want to run. The Select will show which records will be deleted. The other will just delete them.

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

rocky_498 (1/10/2014)
Thanks, this topic is Delete

May I ask why you deleted your original question? To be honest it is bad form especially since it is possible others my have the same question you had and now will have no idea what you had asked.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

rocky_498 (1/10/2014)
Thanks, this topic is Delete

Deleting your original question is a really good way to have people mentally black ball you in the future. 😉

--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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

pietlinden (1/10/2014)
So sue me.

Pretty much an uncalled for statement.

--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)