Using Update in Then

[sameermast]

Hello All

I'm a new member of this group and desperately need the help of experts.

I wish to execute an update statement in the THEN condition of the CASE in a select query i.e.

select pp.productpurchaseid,pps.productpurchasesupportid, 'enddate' =
CASE
WHEN pps.enddate IS NULL THEN
CASE
WHEN DATEADD( month , 36, p.ReleaseDate) > DATEADD(month , 12, salesclosuredate)
THEN (update productpurchasesupport set endate = DATEADD( month , 36, p.ReleaseDate)where pps.productpurchaseid = pp.productpurchaseid)
ELSE (update productpurchasesupport set endate = DATEADD (month , 12, salesclosuredate)where pps.productpurchaseid = pp.productpurchaseid)
END
ELSE pps.enddate
END
from product p inner join productpurchase pp on ( p.productid = 1 and p.productId = pp.productId)
inner join productpurchasesupport pps on (pp.productpurchaseId = pps.productpurchaseid )


Pls help me if the update and select can be done like this in a single query or am i just goin wrong with some syntax.?????
The query is working fine if there is no update statement.
Thanks in advance

Sameer

[rmiao]

No, you can't run update in select statement.

[dbwhz]

There is no need for a select statement if you are trying to update the table....or is that what you are trying to do?

Create a Update query with all the Case statements and then do a select...if you wish to update the table

[zlp321002]

--usage Is wrong
--example:
select
A,
B,
C=case when c='a' then 1
when c='b' then 2
when c='c' then 3
end
from
Table

[Rawhide]

Update pps
Set pps.endate = IsNull(pps.enddate, Case 
		When DATEADD(month, 36, p.ReleaseDate) > DATEADD(month, 12, salesclosuredate) Then DATEADD(month, 36, p.ReleaseDate)
		Else DATEADD(month, 12, salesclosuredate)
		End
From product p 
Inner Join productpurchase pp On p.productid = 1 and p.productId = pp.productId
Inner Join productpurchasesupport pps On pp.productpurchaseId = pps.productpurchaseid

Select pp.productpurchaseid, pps.productpurchasesupportid, pps.enddate
From product p 
Inner Join productpurchase pp On p.productid = 1 and p.productId = pp.productId
Inner Join productpurchasesupport pps On pp.productpurchaseId = pps.productpurchaseid