update table using condetion/case

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

SQL Server 2000 Forums

Transact-SQL (2000)

update table using condetion/case

Author

Topic

skativarapu
Starting Member

13 Posts

Posted - 2006-05-09 : 14:14:14

I have a function which returns a set of values and i have to use the result set to compare with another table and set a flag (0/1) in that column.but this takes a little long to execcute can anyone give a bettter solution for this.

update count set Count= 0
where loan_id in (select * from dbo.fnX())

update count set Count= 1
where loan_id not in (select * from dbo.fnX())

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-09 : 15:11:03

How about taking the values from the function and holding them in a TABLE variable so the function isn't called multiple times:

declare @myIds TABLE (id int)
insert into @myIds(id)
select * from dbo.fnX())

update count set count = 0
where loan_id in (select id from @myIds)

update count set count = 1
where loan_id in (select id from @myIds)

OR

I think the following might also do the trick and probably be faster

update count
set count = 0
from count inner join @myIds mi on @mi.Id = count.loan_id

update count
set count = 1
from counter left outer join @myIds mi on mi.Id = count.load_id
where mi.Id is NULL

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

skativarapu
Starting Member

13 Posts

Posted - 2006-05-10 : 16:44:03

with little changes it worked well and the most imp thing is time,it used to take 4min for the one i have to run and now it taked just 5sec
****joins**** and temp tables

thanks
Dalton

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-10 : 17:21:51

Glad to help.

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources