how can i do? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how can i do?

Author

Topic

zakeer
Starting Member

20 Posts

Posted - 2008-03-28 : 07:36:30

how can i delete duplicate rows in a table using cursors?

Thanks & Regards
Zakeer

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-28 : 07:38:33

any particular reason you want a cursor for this?

Em

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-03-28 : 07:45:23

What is the reason for doing this through a cursor?

zakeer
Starting Member

20 Posts

Posted - 2008-03-28 : 07:45:55

I HAVE A TABLE LIKE THIS

EID ENAME

1 A
2 B
3 C
1 A
2 B
3 C
1 A
2 B
3 C
1 A
2 B
3 C

ACTUALLY I FACED THIS QUESTION IN AN INTERVIEW . HE ASKED ME TO REMOVE THE DUPLICATE ROWS.

I TOLD HIM ,I'LL COPY DISTINCT ROWS INTO ONE MORE TABLE AND I'LL DELETE THE ROWS IN MAIN TABLE AND AGAIN I'LL COPY ALL DISTINCT ROWS INTO THE MAIN TABLE.

BUT HE SAID IF U DO LIKE THIS FOR SOME PERIOD THE MAIN TABLE WILL BE EMPTY.

HIS REQUIREMENT IS HE DONT WANT TO EMPTY MAIN TABLE. WITHOUT DOING THIS WE HAVE TO DO

PLEASE HELP ME.

Thanks & Regards
Zakeer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 07:56:00

DELETE f
FROM (SELECT ROW_NUMBER() OVER EID, ENAME ORDER BY EID) AS RecID) AS f
WHERE RecID > 1

E 12°55'05.25"
N 56°04'39.16"

zakeer
Starting Member

20 Posts

Posted - 2008-03-31 : 14:25:03

THANKS.... BUT THIS IS NOT GETTING WORK IN SQL SERVER 2000.

Server: Msg 195, Level 15, State 10, Line 1
'ROW_NUMBER' is not a recognized function name.

Thanks & Regards
Zakeer

QAZAFI
Yak Posting Veteran

50 Posts

Posted - 2008-03-31 : 15:43:00

Hi Dear
This is SQL Server 2005 Forums. so we only discuss SQL Server 2005 issues here
Thanks

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-31 : 18:56:40

quote:
Originally posted by zakeer

BUT HE SAID IF U DO LIKE THIS FOR SOME PERIOD THE MAIN TABLE WILL BE EMPTY.

That's what transactions are for. If you do it in a transaction what you said will work, in the sense that nobody else's transactions will ever see the empty table. Unfortunately when you get people who spray NOLOCK bullshit all over the place things break down rapidly...

QAZAFI
Yak Posting Veteran

50 Posts

Posted - 2008-03-31 : 21:31:18

Hi Dear
Please have a Look on this topic it will i think cover what you ar looking for
http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/

Many thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-31 : 21:37:43

quote:
Originally posted by LoztInSpace

quote:
Originally posted by zakeer

BUT HE SAID IF U DO LIKE THIS FOR SOME PERIOD THE MAIN TABLE WILL BE EMPTY.

That's what transactions are for. If you do it in a transaction what you said will work, in the sense that nobody else's transactions will ever see the empty table. Unfortunately when you get people who spray NOLOCK bullshit all over the place things break down rapidly...

That's actually not what transactions are for.

- Jeff
http://weblogs.sqlteam.com/JeffS

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-31 : 22:24:28

quote:
Originally posted by jsmith8858

That's actually not what transactions are for.

How so? Are they not to make sure the database stays consistent - i.e. other sessions don't see your work in progress but only the final outcome?

Run the following line by line in one session:


create table test(c int)

insert into test values(1)
insert into test values(2)
insert into test values(3)
insert into test values(4)


begin transaction
delete from test

RUN SELECT * from test in another session here. It will block (or show you the full table depending on your config)


insert into test values(2)
insert into test values(3)
commit

At no time does the other session see the empty table.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-04-01 : 09:58:47

Transactions are used if you have many changes to make to multiple tables all at once, and want either the entire operation to succeed or to fail. They are not so you can delete all the rows from a table and then refill that table without other processes "noticing". If you delete rows from a table and then refill, you have issues with foreign key constraints, cascading updates/deletes, identities, triggers, etc. Using a transaction does not solve those problems in any way. In short, you should simply remove what you need to and don't touch what should remain.

- Jeff
http://weblogs.sqlteam.com/JeffS

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-04-01 : 19:29:15

While you may have an issue with his method, for the purpose of my comment I could not care less if he clears a table then re-inserts or deletes just those rows he wants removed. The fact stands that however he does it, nobody would ever see the "work in progress" if he used transactions. I am not aware of any definition that makes the distinction between changes to single or multiple tables (or individual or multiple rows for that matter). I stand by my statement that using transactions are precicely for preventing others from seeing your partialy completed DML, however crap you may deem it to be.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources