January 17, 2006 at 10:02 am
I'm looking for a way to use the UPDATE command in SQL 2000 to update one field in one table using criteria from multiple tables. The following code is what I finally came up with to get the job done but I think there must be a better way to do this. I've looked all over the Internet and SQLServerCentral trying to find an example I can use. Please note, I want to use this code in a stored procedure.
Here's the code:
/* The exec pulls out all the records that meet the criteria and saves them in an Excel file so the user knows what will be put on hold.
*/
EXEC master..xp_cmdshell 'bcp "exec mas500_app..usp_PutOnHoldReport" queryout c:\Results\PutOnHold\PutOnHold.csv -c -q -t","'
Go
/*
I tried temporary tables and table variables here with no success.
*/
select a.custid
into OnHold
from tarcustomer a,
tarcuststatus b
where a.custkey = b.custkey
and a.hold = 0
and (b.agecat2amt > 0
or b.agecat3amt > 0
or b.agecat4amt > 0)
and a.custid <> 1492
and a.custid <> 327
and a.custid <> 468
and a.custid <> 506
and a.custid <> 622
anda.custid <> 573
anda.custid <> 51
anda.custid <> 686
andmaster.dbo.UDF_FilterStr(a.custid,'[^0-9.]') > 0
order by a.custname
update tarcustomer
set hold = 1
from tarcustomer a, onhold b
where
a.custid = b.custid
Drop Table OnHold
Basically, this code finds all the accounts that have amounts in the Aging categories (60 days, 90 days, etc.) but have not yet been put on hold. The update needs to put all those accounts on hold.
January 17, 2006 at 10:25 am
This should work. Your use of the temp table is not needed. The update can also be written using inner joins.
update tarcustomer
set hold = 1
from tarcuststatus b
where tarcustomer.custkey = b.custkey
and tarcustomer.hold = 0
and (b.agecat2amt > 0 or b.agecat3amt > 0 or b.agecat4amt > 0)
and tarcustomer.custid not in (51,327,468,506,573,622,686,1492)
and master.dbo.UDF_FilterStr(a.custid,'[^0-9.]') > 0
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply