best way to update sql tables - 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

best way to update sql tables

Author

Topic

lollybanty
Starting Member

3 Posts

Posted - 2010-03-08 : 09:02:14

My organisation needs to update value in a column with another value that belongs to the same id. I have used a cursor script i got online but its taking forever to finish.

The script is below. Is there any better way of doing this?

if exists (select * from sysobjects where name = N'UPDATE_BALANCE')

drop procedure UPDATE_BALANCE

go

CREATE PROCEDURE UPDATE_BALANCE

AS

DECLARE @acid nchar(24)
DECLARE @bal_amt numeric (18,3)
-- Declare cursor
DECLARE get_acid CURSOR FOR
SELECT acid, bal_amt
FROM ac_bal_type
WHERE bal_type = 660

BEGIN
-- Open cursor
OPEN get_acid
-- Fetch cursor for the first time
FETCH get_acid INTO @acid, @bal_amt
-- Fetch records in loop until the last one is reached
WHILE @@fetch_status = 0
BEGIN

update ac_bal_type
set bal_amt = @bal_amt
where acid = @acid
and bal_type = 500

FETCH get_acid INTO @acid, @bal_amt
END

-- Close cursor
CLOSE get_acid
DEALLOCATE get_acid
END

raky
Aged Yak Warrior

767 Posts

Posted - 2010-03-08 : 09:07:04

[code]
You dont need all these things ( cursor etc )
simply use this update statement
UPDATE AC
SET bal_amt = BC.bal_amt
FROM ac_bal_type AC
INNER JOIN ac_bal_type BC ON AC.acid = BC.ac_bal_type AND BC.bal_type = 660
WHERE AC.bal_type = 500
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-08 : 09:09:25

First take a backup of ac_bal_type (in case if the update is wrong you can get original data) and run this query

update t1
set t1.bal_amt = t2.bal_amt
from ac_bal_type as t1 inner join ac_bal_type as t2
on t1.acid = t2.acid
where t1.bal_type = 500 and t2.bal_type = 660

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-08 : 09:09:50

Madhivanan

Failing to plan is Planning to fail

raky
Aged Yak Warrior

767 Posts

Posted - 2010-03-08 : 09:12:30

quote:
Originally posted by madhivanan

First take a backup of ac_bal_type (in case if the update is wrong you can get original data) and run this query

update t1
set t1.bal_amt = t2.bal_amt
from ac_bal_type as t1 inner join ac_bal_type as t2
on t1.acid = t2.acid
where t1.bal_type = 550 and t2.bal_type = 660

Madhivanan

Failing to plan is Planning to fail

small typo

t1.bal_type = 500 not 550

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-08 : 09:24:14

quote:
Originally posted by raky

quote:
Originally posted by madhivanan

First take a backup of ac_bal_type (in case if the update is wrong you can get original data) and run this query

update t1
set t1.bal_amt = t2.bal_amt
from ac_bal_type as t1 inner join ac_bal_type as t2
on t1.acid = t2.acid
where t1.bal_type = 550 and t2.bal_type = 660

Madhivanan

Failing to plan is Planning to fail

small typo

t1.bal_type = 500 not 550