rutledgj
asked on
t-sql looping through rows in a table
I have a need to loop through the rows of a temp table, compare certain columns, and perform an update on same columns.
I'm trying to accomplish this within a stored proc (sql server 2000).
Can someone provide sample code on how to do this or suggest a better way?
Note: I'm not to worried about efficiency at this point.
Thanks,
Rut
I'm trying to accomplish this within a stored proc (sql server 2000).
Can someone provide sample code on how to do this or suggest a better way?
Note: I'm not to worried about efficiency at this point.
Thanks,
Rut
Use cursors
>Note: I'm not to worried about efficiency at this point.
note: you should ALWAYS worry about efficiency...
>I have a need to loop through the rows of a temp table, compare certain columns, and perform an update on same columns.
can you explain a bit in details?
usually, a simple UPDATE with CASE syntax can do the job.
note: you should ALWAYS worry about efficiency...
>I have a need to loop through the rows of a temp table, compare certain columns, and perform an update on same columns.
can you explain a bit in details?
usually, a simple UPDATE with CASE syntax can do the job.
declare @col varchar(10),
@co2 varchar(10)
declare c cursor for select column1, column2 from yourtable
open c
fetch next from c into into @co1 , @co2
while @@fetch_status = 0
begin
if @co1= 1
update yourtable set @col=3 where @co2= @co2
fetch next from c into into @co1 , @co2
end
close c
deallocate c
go
@co2 varchar(10)
declare c cursor for select column1, column2 from yourtable
open c
fetch next from c into into @co1 , @co2
while @@fetch_status = 0
begin
if @co1= 1
update yourtable set @col=3 where @co2= @co2
fetch next from c into into @co1 , @co2
end
close c
deallocate c
go
Hi rutledgj,
do you realy need to loop through all the records this is very slow and totaly contary to set based processing
rather tell us your table structure and what you are tryong to update and we will try help you avoid cursors
rutledgj,
> Note: I'm not to worried about efficiency at this point
but soon you will be :)
HTH
R.
do you realy need to loop through all the records this is very slow and totaly contary to set based processing
rather tell us your table structure and what you are tryong to update and we will try help you avoid cursors
rutledgj,
> Note: I'm not to worried about efficiency at this point
but soon you will be :)
HTH
R.
Suppose you have all the conditions linked
Update #temp set col1 = val1, col2 = val2, col3 = val3
where col1 in (select col1 from #temp where col1 = someval1) and
col2 in (select col1 from #temp where col2 = someval2) and
col2 in (select col1 from #temp where col3 = someval3)
If the columns you have are independant then break up the above query as
Update #temp set col1 = val1
where col1 in (select col1 from #temp where col1 = someval)
and repeat it for the number of columns
Update #temp set col1 = val1, col2 = val2, col3 = val3
where col1 in (select col1 from #temp where col1 = someval1) and
col2 in (select col1 from #temp where col2 = someval2) and
col2 in (select col1 from #temp where col3 = someval3)
If the columns you have are independant then break up the above query as
Update #temp set col1 = val1
where col1 in (select col1 from #temp where col1 = someval)
and repeat it for the number of columns
ASKER
OK.
My temp table is like this:
act_id, prop_id, act_date, prop_date, act_name, prop_name, etc
In the above table, act_id WILL always = prop_id but prop_date may not be the same as act_date. So I need to update the prop_date with the act_date.
Rut
My temp table is like this:
act_id, prop_id, act_date, prop_date, act_name, prop_name, etc
In the above table, act_id WILL always = prop_id but prop_date may not be the same as act_date. So I need to update the prop_date with the act_date.
Rut
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.