Cursor- For Update Option - 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 2000 Forums

SQL Server Development (2000)

Cursor- For Update Option

Author

Topic

pravin14u
Posting Yak Master

246 Posts

Posted - 2006-08-12 : 06:47:22

hi all,

i am tryin to use the FOR UPDATE option for a table with duplicate rows.. but i cant do it as FOR UPDATE option wants the table to have primary key... is thr any way in which i can overcome this problem?

pravin14u
Posting Yak Master

246 Posts

Posted - 2006-08-12 : 06:53:59

I am using the "FOR UPDATE" option with a cursor in the above scenario

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-12 : 06:54:47

Can you post code and table structure what you are trying to do

Chirag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-12 : 07:07:29

quote:
Originally posted by pravin14u

hi all,

i am tryin to use the FOR UPDATE option for a table with duplicate rows.. but i cant do it as FOR UPDATE option wants the table to have primary key... is thr any way in which i can overcome this problem?

create an identity as primary key in the table

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-12 : 07:38:01

Don't use a cursor?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pravin14u
Posting Yak Master

246 Posts

Posted - 2006-08-16 : 02:27:37

fine, how can i accompalish the task without cursors(deleting the duplicate rows(i,e) if thr r 3 duplicate, i need to delete only 2 of them)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-16 : 03:12:23

[code]-- create temp table for testing
create table #temp
(
data int
)

-- insert some test data
insert into #temp
select 10 union all
select 10 union all
select 10 union all
select 20 union all
select 20 union all
select 30 union all
select 40

select * from #temp

/*
data
-----------
10
10
10
20
20
30
40

(7 row(s) affected)
*/

alter table #temp add id int identity(1,1)

select * from #temp
/*
data id
----------- -----------
10 1
10 2
10 3
20 4
20 5
30 6
40 7

(7 row(s) affected)
*/

delete t
from #temp t
where t.id not in (select min(id) from #temp x where x.data = t.data)

select * from #temp
/*
data id
----------- -----------
10 1
20 4
30 6
40 7

(4 row(s) affected)
*/

alter table #temp drop column id

select * from #temp
/*
data
-----------
10
20
30
40

(4 row(s) affected)
*/
-- drop the table after testing
drop table #temp
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources