CURSOR READ ONLY - 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

General SQL Server Forums

New to SQL Server Programming

CURSOR READ ONLY

Author

Topic

ag_ss
Starting Member

48 Posts

Posted - 2006-04-25 : 07:13:31

select * from new_iba_3

DECLARE new_cur2 CURSOR FOR select * from new_iba_3 order by rid, attr, val for update of rid;
open new_cur2

DECLARE @tracepoint NUMERIC,
@ibaid NUMERIC,
@rid NUMERIC,
@attr NUMERIC,
@val NVARCHAR(1024),
@src_iba NUMERIC,
@src_link NUMERIC,
@rowid NUMERIC,
@new_rec_ibaid NUMERIC,
@new_rec_rid NUMERIC,
@new_rec_attr NUMERIC,
@new_rec_val NVARCHAR(1024),
@new_rec_orig_rid NUMERIC

FETCH NEXT FROM new_cur2 INTO @new_rec_ibaid,@new_rec_rid,@new_rec_attr,@new_rec_val,@new_rec_orig_rid
delete from new_iba_3 where current of new_cur2; --HERE

close new_cur2;
deallocate new_cur2;

select * from new_iba_3

WHEN I TRY TO DELETE FROM new_iba_3 IT GIVES CURSOR IS READ ONLY
BUT UPDATE WORKS ON THIS TABLE I HAVE NOT DECLARED THIS CURSOUR AS READ ONLY THEN WHY IT IS GIVING THIS ERROR

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-25 : 07:28:11

Please don't post multiple times. See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65164.

BOL (under DECLARE CURSOR) says:

If OF column_name [,...n] is specified, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

ag_ss
Starting Member

48 Posts

Posted - 2006-04-25 : 07:51:40

But i m trying to delete the row
update is working fine
and if i remove order by clause delete will start work
but i need order by

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-25 : 08:06:59

Does this help?

http://sqljunkies.com/Forums/ShowPost.aspx?PostID=1242

I don't see how your update is working fine though.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-04-25 : 11:28:01

skip cursors altogether....
just write a DELETE from tableA WHERE x=y statement.

Cursors are bad....in usage and performance.

X002548
Not Just a Number

15586 Posts

Posted - 2006-04-25 : 13:45:57

not to mention SELECT *

http://weblogs.sqlteam.com/brettk/archive/2004/04/22/1272.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

ag_ss
Starting Member

48 Posts

Posted - 2006-04-26 : 01:39:41

how it can solve my problem

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-26 : 01:42:53

Why did you use Cursors to delete data?

Madhivanan

Failing to plan is Planning to fail

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-04-26 : 02:01:14

Do not cursor it will slow down the performance of your program, Instead try that with simple delete statement to delete the data.

ag_ss
Starting Member

48 Posts

Posted - 2006-04-27 : 05:54:04

i need to use cursor and need orbey for my logic
how to use both

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-27 : 06:02:20

quote:
Originally posted by ag_ss

i need to use cursor and need orbey for my logic
how to use both

Maybe you can
1. explain what you are trying to do
2. provide a simplified table structure that is able to illustrate your requirement and not your full complicated please

3. some sample data
4. the expected result that you want

I am sure the kind SQLTeamers will point you in the right direction.

ag_ss
Starting Member

48 Posts

Posted - 2006-04-27 : 06:21:50

okay i write in simple words i have 2 tables. there is no unique key

table1 table2

col1 col2 col3 col1 col2 col3

1 101 one 1 101 one
1 101 two 1 101 three

i wanna make 2 cursors which takes values from table1 and table2 in order by col1,col2,col3

(for first row)cur1 compares vaules from cur2 if all values are same delete row from table2 and if not delete from table1

ag_ss
Starting Member

48 Posts

Posted - 2006-04-27 : 06:34:49

and result should be

col1 col2 col3

1 101 one
1 101 three

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-27 : 08:41:36

is this what you want ?


delete 	t2
from	table1 t1 inner join table2 t2
on	t1.col1	= t2.col1
and	t1.col2	= t2.col2
and	t1.col3	= t2.col3

ag_ss
Starting Member

48 Posts

Posted - 2006-04-27 : 08:53:08

nope

i need to delete few rows from t1 and few from t2

its like retaining attributes whic r not updated and removing which r updated

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-04-27 : 10:01:44

How do you know which you have to delete from which table?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-27 : 21:38:17

"there is no unique key"
What is the relationship of these 2 tables ? In real life, it does not make sense to me to delete records from one table or another base on the sequence of ordering.

The following crappy solution will give you want you want base on the sample & result that you posted. And is definately not an efficent solution


-- create the data for testing 
create table table1
(
	col1	int,
	col2	int,
	col3	varchar(10)
)

create table table2
(
	col1	int,
	col2	int,
	col3	varchar(10)
)

insert into table1
select	1, 101, 'one' union all
select  1, 101, 'two'

insert into table2
select	1, 101, 'one' union all
select  1, 101, 'three'
-- 

-- processing
declare	@table1 table
(
	id	int identity(1,1),
	col1	int,
	col2	int,
	col3	varchar(10)
)

declare	@table2 table
(
	id	int identity(1,1),
	col1	int,
	col2	int,
	col3	varchar(10)
)

-- insert the data into the table variable for generation of a unique sequential id
insert into @table1(col1, col2, col3) select col1, col2, col3 from table1 order by col1, col2, col3
insert into @table2(col1, col2, col3) select col1, col2, col3 from table2 order by col1, col2, col3

-- Delete the records from table variable
-- if all values are same delete row from table2 and if not delete from table1
delete 	t2
from	@table1 t1 inner join @table2 t2
	on	t1.id	= t2.id
	and	t1.col1	= t2.col1
	and	t1.col2	= t2.col2
	and	t1.col3	= t2.col3

-- base on the table variable and delete the actual table
delete 	t1
from	@table1 t1 inner join @table2 t2
	on	t1.id	= t2.id
	and	(	
			t1.col1	<> t2.col1
		or	t1.col2	<> t2.col2
		or	t1.col3	<> t2.col3
		)

delete	t
from	table1 t left join @table1 t1
	on	t.col1	= t1.col1
	and	t.col2	= t1.col2
	and	t.col3	= t1.col3
where	t1.col1	is null
	
delete	t
from	table2 t left join @table2 t2
	on	t.col1	= t2.col1
	and	t.col2	= t2.col2
	and	t.col3	= t2.col3
where	t2.col1	is null
	
-- Clean Up
drop table table1
drop table table2

emfami
Starting Member

1 Post

Posted - 2007-12-09 : 09:10:56

order by is your problem .
dont use order by in this line:

DECLARE new_cur2 CURSOR FOR select * from new_iba_3 ~~order by rid, attr, val~~ for update of rid;

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-09 : 23:01:45

heh. i'm sure the OP will be back any minute now to read that...

elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-10 : 01:12:55

quote:
Originally posted by emfami

order by is your problem .
dont use order by in this line:

DECLARE new_cur2 CURSOR FOR select * from new_iba_3 ~~order by rid, attr, val~~ for update of rid;

Read second post of OP in this topic
Also if you want to delete duplicates then refer http://sqlteam.com/forums/topic.asp?TOPIC_ID=6256

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources