Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    20

    Unanswered: Update Multiple Rows in single sql statement

    I am using DB2 9.5 UDB on windows

    I want to update multiple rows in a single sql statement.

    I have a table which has an id,date start, date end, sequence.
    The last row has a date end of 9999-12-31 and date start 01-01-2008
    the row before has a null date end and date start 01-01-2007
    the date end of this row needs setting to the previous rows date start minus 1 day (31-12-2007)

    I have created this query which gets me the rows with the correct dates
    and sequence
    SELECT A.id, A.SEQUENCE, A.DATE_start, DATE(B.DATE_start) - 1 DAY AS DATE_end1, B.SEQUENCE, B.DATE_start
    FROM HOLD A, HOLD B
    WHERE A.id = B.id
    AND A.DATE_end IS NULL
    AND A.SEQUENCE = ( B.SEQUENCE -1)

    This returns
    ID SEQUENCE DATE_start DATE_end1 SEQUENCE DATE_start
    --------------- ----------- -------------- ------------ ----- ------------
    200000 5 2004-07-12 31/07/2004 6 2004-08-01
    200000 6 2004-08-01 29/07/2007 7 2007-07-30
    200000 7 2007-07-30 14/10/2007 8 2007-10-15

    i need to update row 5,6,7 with the date end1 value.

    I have tried various methods - i am trying to do this with sql only

    UPDATE HOLD
    SET DATE_end =
    ( SELECT DATE(B.DATE_start) - 1 DAY
    FROM HOLD A, HOLD B
    WHERE A.id = B.id
    AND A.DATE_end IS NULL
    AND A.SEQUENCE = ( B.SEQUENCE -1))

    this returns a -811.

    I have tried other where statements but this does not work.

    Advice whould be greatly appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You have to correlate the subselect to the table being updated, something like:

    Code:
    UPDATE HOLD A
    SET DATE_end = 
    ( SELECT DATE(B.DATE_start) - 1 DAY 
      FROM HOLD B
      WHERE A.id = B.id
      AND  A.SEQUENCE = ( B.SEQUENCE -1))
    WHERE A.DATE_end IS NULL
    Disclaimer: not tested.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2009
    Posts
    20

    thanks

    That worked as i needed it to

    Looks like I was trying to be to clever with the sql i was writing

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It's really not at all confusing if you think logically, the way SQL does:
    - what do I want to update? Table HOLD; I'll call it "a", because it's shorter;
    - what rows? Those where DATE_END is NULL ;
    - what column? DATE_END;
    - what do I want to set it to? Something from the table HOLD; since it will be a different set of rows I'll call it "b" to avoid confusing it with "a";
    - now, for each matching row in "a", what row in "b" do I need? That with the same value of ID and next in SEQUENCE.
    - what happens if there is no record with the same ID and next in sequence? Nothing; an empty set will be returned, and DATE_END will be set to NULL, that is, no change.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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