Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2011
    Posts
    38

    Unanswered: UPDATE from SELECT

    What is wrong with this SQL command?
    Code:
    UPDATE NEWTABLE n
    SET n.FREQ 
    SELECT COUNT(DISTINCT o.ID) 
    FROM ORIGINALTABLE o
    GROUP BY o.CODE
    WHERE o.CODE = f.CODE
    All I'm trying to do is to get the count of rows containing each CODE in the ORIGINALTABLE and store them in the FREQ column of the row with the same CODE in the NEWTABLE

  2. #2
    Join Date
    Apr 2011
    Posts
    38
    Nevermind, I found the answer: I should use HAVING instead of WHERE:
    Code:
    UPDATE NEWTABLE n
    SET n.FREQ = (
    SELECT COUNT(DISTINCT o.ID) 
    FROM ORIGINALTABLE o
    GROUP BY o.CODE
    HAVING o.CODE = f.CODE)

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I should use HAVING instead of WHERE:
    No! It's not the point.
    Put the select into parentheses as you did.
    The column(s) to be updated are always column(s) of target table(NEWTABLE n).
    So, the column(s) are not neccesary to be qualified.

    Code:
    UPDATE NEWTABLE n
       SET FREQ
        = (SELECT COUNT(DISTINCT o.ID) 
            FROM  ORIGINALTABLE o
            WHERE o.CODE = n.CODE
          )

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    As long as you are aware that you update ALL rows in NEWTABLE... You don't have a WHERE condition on the UPDATE itself (just on the nested subselect), so all rows will be processed.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486

    Update from SELECT

    Nice catch!

    Could have lead to a nasty surprise. . .

    Hopefully, a backup will be taken before anything is actually changed

  6. #6
    Join Date
    Apr 2011
    Posts
    38
    Thanks for your comments. I tried WHERE and it didn't work.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    it didn't work
    What does it mean?
    Did you got error message(s)?
    Were the results of update different from what you expected?

    Anyway, please state more concretely.

Posting Permissions

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