Help with looping and update statement

[mark1110]

Hi,

I am using Sybase 11.9. I need to convert an update statement into an update statement using a loop so I can do a commit so I don't get an syslog full error. I have 80,000 records to update. Here are a couple of update statements I have:

update avenue_agent_appt
set v.addr1 = l.addr1
,v.addr2 = l.addr2
,v.city = l.city
,v.state = l.state
,v.zip_cd = l.zip_cd
from avenue_agent_appt v, #av_use u, producer_location l
where v.tin = l.tin
and v.tin = u.tin
and u.use_ind = "ALL"
and u.use_st in (NULL, '', ' ')
and l.location_cd = u.location_cd


update avenue_agent_appt
set v.mail_addr1 = l.addr1
,v.mail_addr2 = l.addr2
,v.mail_city = l.city
,v.mail_state = l.state
,v.mail_zip_cd = l.zip_cd
from avenue_agent_appt v, #av_use u, producer_location l
where v.tin = l.tin
and v.tin = u.tin
and l.location_cd = u.location_cd
and u.use_ind ="PHYS"

Do I need to use a loop for each update statement or could I combine several update statements into one loop.

Thanks,

Mark

 

[PDreyer]

Use a where clause to prevent updating the same rows in the loop
or if you have a unique id you can use that.
depending on your data and log size
change the row count to e.g. 50000
example 1:

select * into #t1 from master..spt_values where type='P'

declare @rowcnt int
select @rowcnt=50
set rowcount 50 --@rowcnt
while @rowcnt=50
begin
  update #t1 set low=high where low!=high
  select @rowcnt=@@rowcount
end
set rowcount 0

drop table #t1

example 2:

select * into #t1 from master..spt_values where type='P'

declare @id int, @rowcnt int
select @id=1, @rowcnt=50
while @rowcnt=50
begin
  update #t1 set low=high where number between @id and @id+@rowcnt-1
  select @rowcnt=@@rowcount, @id=@id+@rowcnt
end

drop table #t1