how to update multiple columns in sql statment

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)

how to update multiple columns in sql statment

Author

Topic

bsharris
Starting Member

3 Posts

Posted - 2004-11-02 : 11:10:41

I am almost sure I can update variables columns in one select/case type
statement, but having problems working out the syntax.

I have a table with transactions - with tran types as the key.

in this example, types = A,B,C ,D.

in this first example I am updating the sum of QTY to value t_A based on
tran types =A.

I want to sum qty for other types as well and place them in
their appropriate columns.

can I perform sub query/case to update with the same where clause
but for types B,C and D?? I also have to insert for specific lot numbers each sum values.

Create table #t_reconcile(
t_lot_number int not null,
t_A float,
t_B float,
t_C float,
t_D float)

insert #t_reconcile

select t.lot_number, sum(t.qty)
from i , t
where i._id = t.event_id
i.transaction_type = 'A'
group by t.lot_number
order by t.lot_number

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-02 : 11:58:43

I don't really understand your question -- how about some sample data and desired results?

Are you trying to do a cross-tab or a pivot?


select ID,
  sum(Case when Type='A' then amount else 0 end) as A_Total,
  sum(Case when Type='B' then amount else 0 end) as B_Total,
  sum(Case when Type='C' then amount else 0 end) as C_Total,
  sum(Case when Type='D' then amount else 0 end) as D_Total
from 
  yourtable
group by ID

???

you can update your table using that SQL statement if you like, to update your 4 values (A,B,C,D). better to just keep it as a query instead of constantly re-writing/restoring values in tables, though.

- Jeff

bsharris
Starting Member

3 Posts

Posted - 2004-11-02 : 12:15:30

yes that is what will work.

in one of the case statements can I specify:
sum(Case when Type='D' and amount > 0 then amount else 0 end) as D_Total

as I tried that and get a syntax error on the 'and' statement

bev

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-02 : 12:27:03

I can't tell you what's wrong with the statment if you don't show the whole thing.

- Jeff

bsharris
Starting Member

3 Posts

Posted - 2004-11-02 : 12:31:19

sorry.

*** what I want to do is change this statements
sum(case i.transaction_type when 'ADJ' then t.quantity else 0 end) ,
to do this: (to get only positive values and sum those)
sum(case i.transaction_type when 'ADJ' and t.quantity >= 0 then t.quantity else 0 end)
***************

select t.lot_number,
sum(case i.transaction_type when 'PHYS' then t.quantity else 0 end),
sum(case i.transaction_type when 'AOC' then t.quantity else 0 end),
sum(case i.transaction_type when 'TI' then t.quantity else 0 end),
sum(case i.transaction_type when 'SAMPL' then t.quantity else 0 end),
sum(case i.transaction_type when 'TO' then t.quantity else 0 end),
sum(case i.transaction_type when 'RETRC' then t.quantity else 0 end),
sum(case i.transaction_type when 'ADJ' then t.quantity else 0 end)

from i , t
where
i.inventory_event_id = t.inventory_event_id
and i.transaction_date <= '09/30/2004' and
t.lot_number is not NULL

group by t.lot_number
order by t.lot_number

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-02 : 13:18:42

Exactly as you posted it, in your question "why doesn't this work!?", is the answer. the SQL you wrote does not have your CASE statements constructed that same way -- they are incorrect.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources