Subquery in Update statement

ontheDB · #1 (**permalink**) 03-25-10, 15:43

I would like to do an update on a table as below. But i get the error : ERROR: ILLEGAL SYMBOL ",". SOME SYMBOLS THAT...

I think i could fix this by putting the subquery into the 'WHERE' clause...but that would mean 4 subqueries: one for each criteria.

Is there a easier way to do this ?

Thanks!

<code>
UPDATE NOTICES C,

(SELECT COUNT (*) AS NUM, A.Notice_ID
,A.Notice_Client

FROM NOTICES A

WHERE 1=1
AND A.Notice_Client IN (15)
AND A.Notice_Expire_Time > CURRENT TIMESTAMP
AND A.Notice_Create_Time > '2009-10-13-00.00.00.000000'

GROUP BY A.RSK_ASMT_TRK_ID, A.RSK_IND_ID
ORDER BY NUM DESC
) AS B

SET C.Notice_Expire_Time = CURRENT TIMESTAMP

WHERE B.NUM > 1
AND C.Notice_Client = B.Notice_Client
AND C.Notice_ID = B.Notice_ID
AND C.Notice_Expire_Time > CURRENT TIMESTAMP
AND C.RSK_Notice_Create_Time IN
(SELECT MAX(D.Notice_Create_Time)
FROM NOTICES D
WHERE D.Notice_Client = C.Notice_Client
AND D.Notice_ID = C.Notice_ID
)

</code>

ARWinner · #2 (**permalink**) 03-25-10, 15:58

You cannot update using a join. Try rewriting it using the EXISTS predicate.

Andy

sathyaram_s · #3 (**permalink**) 03-25-10, 16:01

Code:

 
update notices c set  C.Notice_Expire_Time = CURRENT TIMESTAMP
where
(select count(*) FROM NOTICES A WHERE A.Notice_Client IN (15)
AND A.Notice_Expire_Time > CURRENT TIMESTAMP
AND A.Notice_Create_Time > '2009-10-13-00.00.00.000000'
AND C.Notice_Client = A.Notice_Client
AND C.Notice_ID = A.Notice_ID
AND C.Notice_Expire_Time > CURRENT TIMESTAMP
GROUP BY A.RSK_ASMT_TRK_ID, A.RSK_IND_ID ) > 1
and
 C.RSK_Notice_Create_Time IN
(SELECT MAX(D.Notice_Create_Time)
FROM NOTICES D
WHERE D.Notice_Client = C.Notice_Client
AND D.Notice_ID = C.Notice_ID
)

I am not sure if this will satisfy your requirement, but you get the idea of how to write an update stmt in db2 . I suggest you re-write this to improve efficiency, say, using EXISTS (like Andy mentions) and simplifying it ...

ontheDB · #4 (**permalink**) 03-26-10, 09:22

I wasn't able to get the above posts code to work. But I was able to get the below to work. Thanks for your help.

But I am still curious about using the 'Exists' clause. I had tried that earlier, but failed because I still want only a portion of the subquery (i.e. Num > 1).

So would it be:

WHERE EXISTS (subquery which returns all notices amounts incl NUM=1)
or
WHERE EXISTS (subquery which returns only notices amounts where NUM>1)

If it's the second option, how would one do the subquery in just one level ?

Code:

 
update notices c set  C.Notice_Expire_Time = CURRENT TIMESTAMP
where C.NOTICE_ID IN
(SELECT C.NOTICE_ID FROM
(select count(*), C.NOTICE_ID FROM NOTICES A WHERE A.Notice_Client IN (15)
AND A.Notice_Expire_Time > CURRENT TIMESTAMP
AND A.Notice_Create_Time > '2009-10-13-00.00.00.000000'
AND C.Notice_Client = A.Notice_Client
AND C.Notice_ID = A.Notice_ID
AND C.Notice_Expire_Time > CURRENT TIMESTAMP
GROUP BY A.RSK_ASMT_TRK_ID, A.RSK_IND_ID ) AS B
WHERE B.NUM > 1
)

and
 C.RSK_Notice_Create_Time IN
(SELECT MAX(D.Notice_Create_Time)
FROM NOTICES D
WHERE D.Notice_Client = C.Notice_Client
AND D.Notice_ID = C.Notice_ID
)

ARWinner · #5 (**permalink**) 03-26-10, 10:01

I am not sure if I am getting what you want, but wouldn't it just be:

WHERE EXISTS (subquery which returns only notices amounts where NUM>=1)

instead?

Andy

ontheDB · #6 (**permalink**) 03-26-10, 14:39

Quote:

Originally Posted by ARWinner

I am not sure if I am getting what you want, but wouldn't it just be:

WHERE EXISTS (subquery which returns only notices amounts where NUM>=1)

instead?

Andy

Hi Andy,

Do you mean "Num>=1" or "Num>1" ?

I thought that as well, but was not able to create a query which returns notices num > 1.
I was only able to do so by doing a "2-level" query as in Post #4

ARWinner · #7 (**permalink**) 03-26-10, 14:51

Instead of throwing SQL at me that doesn't do what you want, please post the DDL for the relevant tables and then tell me what you are trying to do in a paragraph or two. It is hard to guess your intent from the SQL code.

Andy

tonkuma · #8 (**permalink**) 03-26-10, 17:23

1) I agree Andy's statement.
"It is hard to guess your intent from the SQL code."

It would be better to show sample data and expected result from the sample data.

2) Your subquery may have syntax error.

Quote:

(SELECT COUNT (*) AS NUM, A.Notice_ID
,A.Notice_Client

FROM NOTICES A

WHERE 1=1
AND A.Notice_Client IN (15)
AND A.Notice_Expire_Time > CURRENT TIMESTAMP
AND A.Notice_Create_Time > '2009-10-13-00.00.00.000000'

GROUP BY A.RSK_ASMT_TRK_ID, A.RSK_IND_ID
ORDER BY NUM DESC
) AS B

A.Notice_ID and A.Notice_Client are not in grouping column.
So, aggregate function(for example MAX) is neccesary.

Anyway, here is a query based on my guess at your intent...

Code:

UPDATE NOTICES C
   SET C.Notice_Expire_Time = CURRENT TIMESTAMP 
 WHERE C.Notice_Client IN (15)
   AND C.Notice_Expire_Time > CURRENT TIMESTAMP 
   AND EXISTS
       (SELECT A.RSK_ASMT_TRK_ID
             , A.RSK_IND_ID 
          FROM NOTICES A 
         WHERE A.Notice_Client      = C.Notice_Client
           AND A.Notice_ID          = C.Notice_ID 
           AND A.Notice_Expire_Time > CURRENT TIMESTAMP 
           AND A.Notice_Create_Time > '2009-10-13-00.00.00.000000' 
         GROUP BY
               A.RSK_ASMT_TRK_ID
             , A.RSK_IND_ID 
        HAVING COUNT (*) > 1
       )
   AND C.RSK_Notice_Create_Time
       =
       (SELECT MAX(D.Notice_Create_Time) 
          FROM NOTICES D 
         WHERE D.Notice_Client = C.Notice_Client 
           AND D.Notice_ID     = C.Notice_ID 
       )
;

Lenny77 · #9 (**permalink**) 03-26-10, 17:48

Query looks good, but could be undersized:

Code:

UPDATE NOTICES C
   SET C.Notice_Expire_Time = CURRENT TIMESTAMP 
 WHERE C.Notice_Client IN (15)
   AND C.Notice_Expire_Time > CURRENT TIMESTAMP 
   AND EXISTS
       (SELECT 1  FROM NOTICES A 
         WHERE A.Notice_Client       =   C.Notice_Client
           AND A.Notice_ID              =  C.Notice_ID 
           AND A.Notice_Expire_Time  > CURRENT TIMESTAMP 
           AND A.Notice_Create_Time > '2009-10-13-00.00.00.000000' 
         GROUP BY
               A.RSK_ASMT_TRK_ID
             , A.RSK_IND_ID 
        HAVING COUNT (*) > 1
       )
   AND C.RSK_Notice_Create_Time
       =
       (SELECT MAX(D.Notice_Create_Time) 
          FROM NOTICES D 
         WHERE D.Notice_Client = C.Notice_Client 
             AND D.Notice_ID      = C.Notice_ID 
       )
;

Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech