October 23, 2006 at 11:47 am
Hi,
I have the following update script:
My parameter is category from a web client.
SELECT l.budgetaccountnumber,s.ap1,s.ap2,s.ap3,s.ap4,s.ap5,s.ap6,s.ap7,s.ap8,s.ap9,s.ap10,s.ap11,s.ap12
FROM source_budgetaccounts_categories AS l LEFT JOIN category_deptbudgets_v s
ON l.budgetaccountnumber = s.budgetaccount
WHERE s.categoryname = 'AMKT'
SELECT l.budgetaccountnumber,s.ap1,s.ap2,s.ap3,s.ap4,s.ap5,s.ap6,s.ap7,s.ap8,s.ap9,s.ap10,s.ap11,s.ap12
FROM source_budgetaccounts_categories AS l LEFT JOIN category_deptbudgets_v s
ON l.budgetaccountnumber = s.budgetaccount
WHERE s.categoryname = 'Finance & IT'
The issue, I have a master table that contains all accounts and the data values are posted to this table. Sometimes certain categories do not have the same accounts but in the report all accounts need to be displayed. How can I zero out the accounts that do not exist for a given category in batch update mode. I can't think of an efficient method.
Thanks,
October 23, 2006 at 4:31 pm
First: I have the following update script:
No you don't. Both of those scripts are SELECT statements and don't update anything.
Second: Are you really trying to UPDATE something?
Third: Provide us with your tables and sample data. Then, based on the sample data, show us what you want as a result.
-SQLBill
October 23, 2006 at 5:35 pm
Right,
At the time I posted this I had been going back and forth between SQL Server and a .NET build I was trying to get out to production. I did not provide enough information. After stepping back and going back to my previous posts I realized that my question had already been answered. This is the answer that was posted to me by Nathanael Mann. This was the answer
update Lookup set
[values] = 0
from
(
select l.acct, s.[values]
from
Lookup as l
left
join source as s
on
l.acct = s.acct
where
s.acct is null) as s
where
s.acct = Lookup.acct
Thanks,
October 23, 2006 at 6:31 pm
update L
set [values] = 0
from Lookup as L
left join source as s on l.acct = s.acct
where s.acct is null
AND (L.[values] <> OR L.[values] IS NULL)
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply