Update through cursor - 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

Update through cursor

Author

Topic

qamarzaman
Starting Member

1 Post

Posted - 2009-06-24 : 04:20:01

Hi,
Im trying to update a field in a table through a cursor, and selecting joined tables in the cursor. I want to assign new order_id from the sequence (.nextval),or assign the current id in the sequence (.currval) depending on a column in the sequence. If the value of my_cur.no_of_sites = 1, then assign (.nextval)
elsif
my_cur.no_of_sites > 1 AND the value in a variable is the same as
last fetched into it then use (.currval)
else
(.nextval)
------------------
im pasting the PL CODE im trying to run. Can someone please have a look at it and tell me where am i going wrong.
Many Thanks
----------------------------------

drop sequence seq_order_id;
Col Maxid New_Value _maxid
Select Max(order_Id + 1) Maxid From sales_order;
Create Sequence seq_order_Id
Start With &&_maxid Increment By 1;

declare

cursor order_cur IS
select pi.*, nosv.*
from ph2_item pi inner join
no_of_sites_view nosv
on pi.customer_id = nosv.id
order by pi.customer_id, pi.ship_date
for update of pi.order_id;

custid ph2_item.customer_id%type;
sdate ph2_item.ship_date%type;

BEGIN
--select my_cur.customer_id into custid;
--select my_cur.sgip_date into sdate;
for my_cur in order_cur loop

if my_cur.no_of_sites = 1
then

update ph2_item pi
SET
pi.order_id = seq_order_id.nextval
where pi.order_id = my_cur.order_id;

elsif
my_cur.no_of_sites > 1 AND my_cur.customer_id = custid AND my_cur.ship_date = sdate
then
update ph2_item pi
SET
pi.order_id = seq_order_id.currval;
--where pi.customer_id = my_cur.customer_id

elsif
my_cur.no_of_sites >1
then
pi.order_id = seq_order_id.nextval;

end if;
select my_cur.customer_id into custid;
select my_cur.sgip_date into sdate;
--commit;
end loop;
END;
/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 06:28:53

This is a Microsoft SQL Server forum.
You might want to look at www.dbforums.com for PL-SQL forum.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources