All Forums Database
KVB 124 posts Joined 09/12
17 Dec 2013
Error in update statement

UPDATE CUST_T
FROM (SELECT A.CUST_ID,A.CUST_DT
FROM CUST_T B
INNER JOIN
CUST__HIER A
ON B.LAST_CUST_ID = A.CUST_ID)  UPD1
SET
CASE WHEN IMMED_CUST_DT = '9999-12-31' THEN IMMED_CUST_DT end  =UPD1.CURR_CUST_DT
I am getting error with the updaet syntax.

M.Saeed Khurram 544 posts Joined 09/12
17 Dec 2013

HI,
Try this:

UPDATE CUST_T
FROM (SELECT A.CUST_ID,A.CUST_DT
FROM CUST_T B
INNER JOIN
CUST__HIER A
ON B.LAST_CUST_ID = A.CUST_ID)  UPD1
SET
CASE WHEN IMMED_CUST_DT = '9999-12-31' THEN IMMED_CUST_DT = UPD1.CURR_CUST_DT END

 

Khurram

KVB 124 posts Joined 09/12
17 Dec 2013

Still it gives same error..
I have written case statement in SELECT condition itself.It got solved .
But Is there any other way of writing the case stmt in set?

Raja_KT 1246 posts Joined 07/09
17 Dec 2013

Maybe you can dont you try with merge into statement?
 
similar to
 
MERGE INTO Department
      USING USING (SELECT .......

FROM contact
WHERE contact_number = ....)  a
on (condition a=b)
WHEN MATCHED THEN UPDATE
      SET budget_amount = nd.Budget_Amount
WHEN NOT MATCHED THEN INSERT
VALUES
(....);
you can remove the insert part if not needed.
Cheers,
Raja
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.