Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    102

    Unanswered: Subquery in Update statement

    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>

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You cannot update using a join. Try rewriting it using the EXISTS predicate.

    Andy

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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 ...
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Sep 2003
    Posts
    102
    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
    )
    Last edited by ontheDB; 03-26-10 at 10:26.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  6. #6
    Join Date
    Sep 2003
    Posts
    102
    Quote Originally Posted by ARWinner View Post
    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

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    (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 
           )
    ;
    Last edited by tonkuma; 03-26-10 at 18:32. Reason: Change "SELECT *" in subquery to grouping columns. Add "C.Notice_Client IN (15)".

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up ...and exists (select 1 from notices a...

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •