case

17 Feb 2006 5:27 AM

ichor

can i use case in an update statement?

UPDATE pn
SET pn.payg_tax = pn.witholdingtax,
pn.witholdingtax = 0
FROM @PaymentNotification pn
where witholdingtax > 0 and ato_payment_id is null

UPDATE pn
SET pn.witholdingtax = pn.payg_tax,
pn.payg_tax = 0
FROM @PaymentNotification pn
where witholdingtax > 0 and ato_payment_id is not null

i want to combine teh above 2 statements.

17 Feb 2006 6:00 AM

Stephany Young

You mean like?:

UPDATE
pn
SET
pn.payg_tax=case when ato_payment_id is null then pn.witholdingtax else 0
end,
pn.witholdingtax=case when ato_payment_id is null then 0 else pn.payg_tax
end
FROM
@PaymentNotification pn
where
witholdingtax>0

Show quote

"ichor" <ic***@hotmail.com> wrote in message
news:OwnarL4MGHA.2764@TK2MSFTNGP10.phx.gbl...
> can i use case in an update statement?
>
> UPDATE pn
> SET pn.payg_tax = pn.witholdingtax,
> pn.witholdingtax = 0
> FROM @PaymentNotification pn
> where witholdingtax > 0 and ato_payment_id is null
>
> UPDATE pn
> SET pn.witholdingtax = pn.payg_tax,
> pn.payg_tax = 0
> FROM @PaymentNotification pn
> where witholdingtax > 0 and ato_payment_id is not null
>
> i want to combine teh above 2 statements.
>
>