Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    1

    Unanswered: Update with join problem

    Hello,

    I'm having trouble to 'translate' a select-statement, which provides me with the correct information, into an Update-statement.

    Basically, in case of a merge of 2 items, I want to update a date-field for the item that remains after the merge, with a date-field of the item that'll 'vanish', but only when the date of the 'vanishing' item is greater than that of the remaining item.

    The statement that locates these items is:

    select temp.bsstammnr, temp.schlinh, temp.schlinh1, temp.max_dat
    from (select cast(a.SCHLINH / 100 as integer) as bsstammnr,
    a.schlinh as schlinh,
    a.schlinh1 as schlinh1,
    max(b.datum3) as max_dat
    FROM r002090v a, r002090v b
    WHERE a.vertraend = '3'
    and a.tarif1 in ('11', '12', '21', '22', '23', '24')
    and year(a.anldat) = '2010'
    and a.mkm = 0
    and a.schlinh1 = b.schlinh
    and b.schlinh1 = a.schlinh
    group by cast(a.SCHLINH / 100 as integer),
    a.schlinh, a.schlinh1) as temp, r002090v c
    where temp.schlinh = c.schlinh
    and temp.schlinh1 = c.schlinh1
    and temp.max_dat > c.datum3
    order by bsstammnr

    This query provides the max_dat of the serial number (bsstammnr), within which 1 or more items have been merged to the remaining item (schlinh). It also provides the vanishing item's identifier (schlinh1).

    The results of the above query shows that the date of 5 items need to be updated:

    BSSTAMMNR SCHLINH SCHLINH1 MAX_DAT
    27,947 2,794,702 2,794,713 20,051,125
    147,601 14,760,102 14,760,103 20,071,127
    711,055 71,105,502 71,105,503 20,071,023
    711,503 71,150,301 71,150,302 20,070,928
    714,056 71,405,602 71,405,601 20,080,111

    And the UPDATE statement is causing me loads of trouble. Whatever I try, I get: Result of SELECT more than one row... I did the sensible thing (I think, I'm not an expert) to not refer to the table I want to update within the SELECT statement. But whatever I try... I'm sure the figures -811 and 21000 will haunt me for a couple days. ;-))

    Here's the UPDATE statement:

    Update r000400v x
    Set x.sbeginndat = (select temp.max_dat
    from (select cast(a.SCHLINH / 100 as integer) as bsstammnr,
    a.schlinh as schlinh,
    a.schlinh1 as schlinh1,
    max(b.datum3) as max_dat
    FROM r002090v a, r002090v b
    WHERE a.vertraend = '3'
    and a.tarif1 in ('11', '12', '21', '22', '23', '24')
    and year(a.anldat) = '2010'
    and a.mkm = 0
    and a.schlinh1 = b.schlinh
    and b.schlinh1 = a.schlinh
    group by cast(a.SCHLINH / 100 as integer),
    a.schlinh, a.schlinh1) as temp, r002090v c
    where temp.schlinh = c.schlinh
    and temp.schlinh1 = c.schlinh1
    and temp.max_dat > c.datum3)


    where x.bsstammnr * 100 + x.bsvnr in (select temp.schlinh
    from (select cast(a.SCHLINH / 100 as integer) as bsstammnr,
    a.schlinh as schlinh,
    a.schlinh1 as schlinh1,
    max(b.datum3) as max_dat
    FROM r002090v a, r002090v b
    WHERE a.vertraend = '3'
    and a.tarif1 in ('11', '12', '21', '22', '23', '24')
    and year(a.anldat) = '2010'
    and a.mkm = 0
    and a.schlinh1 = b.schlinh
    and b.schlinh1 = a.schlinh
    group by cast(a.SCHLINH / 100 as integer),
    a.schlinh, a.schlinh1) as temp, r002090v c
    where temp.schlinh = c.schlinh
    and temp.schlinh1 = c.schlinh1
    and temp.max_dat > c.datum3)

    This bit: "x.bsstammnr * 100 + x.bsvnr" represents the remaining item after merge in the target table.

    If anyone'd have any suggestions, I'd be very grateful. Thanks in advance for having a look!

    Gr. W.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    I'm fairly certain that the subquery in the SET clause of your UPDATE statement:
    Code:
    select temp.max_dat
    from (select cast(a.SCHLINH / 100 as integer) as bsstammnr,
                 a.schlinh as schlinh,
                 a.schlinh1 as schlinh1,
                 max(b.datum3) as max_dat
          FROM r002090v a, r002090v b
          WHERE a.vertraend = '3'
            and a.tarif1 in ('11', '12', '21', '22', '23', '24')
            and year(a.anldat) = '2010'
            and a.mkm = 0
            and a.schlinh1 = b.schlinh
            and b.schlinh1 = a.schlinh
          group by cast(a.SCHLINH / 100 as integer),
                   a.schlinh, a.schlinh1
       ) as temp
       INNER JOIN r002090v c ON temp.schlinh = c.schlinh
                             and temp.schlinh1 = c.schlinh1
                             and temp.max_dat > c.datum3
    is missing a correlation condition (specifying the link between x, the table to be updated, and the tables temp and c of the subquery.
    Adding some "WHERE x.col = c.col AND ..." at the very end of the above subquery should achieve what you want.
    Maybe there's a PK-FK relation between tables r000400v and r002090v ?

    The "-811" tells you that you try to update column x.sbeginndat with more than one value (viz all values temp.max_dat returned by the above query).
    If you're sure there is no correlation involved, i.e., that you want all matching rows of x to be updated with the same max_dat value, maybe you want "select MAX(temp.max_dat)" in the subquery?
    Last edited by Peter.Vanroose; 09-05-11 at 12:45.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Your missing correlation condition in the SET subquery might be
    AND a.schlinh = x.bsstammnr * 100 + x.bsvnr

    Like...
    Code:
    UPDATE r000400v x 
       SET x.sbeginndat
         = (SELECT temp.max_dat 
             FROM  (SELECT
                           a.schlinh  as schlinh
                         , a.schlinh1 as schlinh1
                         , MAX(b.datum3) as max_dat 
                     FROM  r002090v a
                         , r002090v b 
                     WHERE a.vertraend = '3' 
                       and a.tarif1 IN ('11', '12', '21', '22', '23', '24') 
                       and YEAR(a.anldat) = 2010 
                       and a.mkm = 0 
                       and b.schlinh  = a.schlinh1
                       and b.schlinh1 = a.schlinh 
                       AND a.schlinh  = x.bsstammnr * 100 + x.bsvnr
                     GROUP BY
                           a.schlinh
                         , a.schlinh1
                   ) as temp
                 , r002090v c
             WHERE temp.schlinh  = c.schlinh
               and temp.schlinh1 = c.schlinh1
               and temp.max_dat  > c.datum3
           )
     WHERE x.bsstammnr * 100 + x.bsvnr
        IN (SELECT temp.schlinh
             FROM  (SELECT
                           a.schlinh  as schlinh
                         , a.schlinh1 as schlinh1
                         , MAX(b.datum3) as max_dat 
                     FROM  r002090v a
                         , r002090v b 
                     WHERE a.vertraend = '3' 
                       and a.tarif1 IN ('11', '12', '21', '22', '23', '24') 
                       and YEAR(a.anldat) = 2010
                       and a.mkm = 0 
                       and b.schlinh  = a.schlinh1
                       and b.schlinh1 = a.schlinh 
                     GROUP BY
                           a.schlinh
                         , a.schlinh1
                   ) as temp
                 , r002090v c
             WHERE temp.schlinh  = c.schlinh 
               and temp.schlinh1 = c.schlinh1 
               and temp.max_dat  > c.datum3
           )
    Note 1: I capitalized keywords like UPDATE, SELECT, FROM, so on...
    Note 2: It may be not neccesary to include the expression cast(a.SCHLINH / 100 as integer)
    in SELECT clause and GROUP BY clause.
    Because, a.SCHLINH used in the expresion was a GROUP BY column.
    Note 3: I removed quotation marks in
    YEAR(a.anldat) = '2010'
    Because, the result of a YEAR() function is INTEGER.
    I guessed that DB2 might do datatype conversion implicitly in your original query.
    Last edited by tonkuma; 09-06-11 at 04:07. Reason: Move additional condition into temp subquery. Add note 3.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I have a question.

    Is it not necessary to specify same conditions for r002090v b and r002090v c as the conditions for r002090v a?
    If it is not necessary, why?
    Last edited by tonkuma; 09-06-11 at 02:53.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Basically, in case of a merge of 2 items,
    I want to update a date-field for the item that remains after the merge,
    with a date-field of the item that'll 'vanish',
    but only when the date of the 'vanishing' item is greater than that of the remaining item.

    The statement that locates these items is:
    ...

    This query provides the max_dat of the serial number (bsstammnr),
    within which 1 or more items have been merged to the remaining item (schlinh).
    It also provides the vanishing item's identifier (schlinh1).

    The results of the above query shows that the date of 5 items need to be updated:
    ...
    I have read again your requirements,
    and I guessed...

    1) number of the remaining item in a serial number (bsstammnr) was one.
    And the candidates of remaining items would be the rows satisfying the conditions:
    WHERE a.vertraend = '3'
    and a.tarif1 in ('11', '12', '21', '22', '23', '24')
    and year(a.anldat) = '2010'
    and a.mkm = 0

    Is it right?

    2) the number of vanishing items in a serial number (bsstammnr) were zero, one, or more.
    And the vanishing items(b) are known from the remaining item(a) by the conditions:
    ... b.schlinh = a.schlinh1
    and b.schlinh1 = a.schlinh

    (If no vanishing item was there, then update would not be necessary for the serial number (bsstammnr).)

    Is it right?


    If my guess was right,
    this example might give you same results as your first query.
    Code:
    SELECT CAST(a.schlinh / 100 AS INTEGER) AS bsstammnr
         , a.schlinh
         , MAX(a.schlinh1) AS schlinh1
         , MAX(b.datum3)   AS max_dat
     FROM  r002090v a
     INNER JOIN
           r002090v b
       ON  b.schlinh1 = a.schlinh
       AND b.schlinh  = a.schlinh1
     WHERE a.vertraend    = '3' 
       AND a.tarif1      IN ('11', '12', '21', '22', '23', '24') 
       AND YEAR(a.anldat) = 2010 
       AND a.mkm          = 0
     GROUP BY
           a.schlinh
     HAVING
           MAX(b.datum3) > MAX(a.datum3)
    and this might be worth to try.
    Code:
    UPDATE r000400v x 
       SET x.sbeginndat
         = (SELECT MAX(b.datum3)
             FROM  r002090v a
             INNER JOIN
                   r002090v b
               ON  b.schlinh1 = a.schlinh
               AND b.schlinh  = a.schlinh1
             WHERE a.schlinh  = x.bsstammnr * 100 + x.bsvnr
           )
     WHERE x.bsstammnr * 100 + x.bsvnr
        IN (SELECT a.schlinh
             FROM  r002090v a
             INNER JOIN
                   r002090v b
               ON  b.schlinh1 = a.schlinh
               AND b.schlinh  = a.schlinh1
             WHERE a.vertraend    = '3' 
               AND a.tarif1      IN ('11', '12', '21', '22', '23', '24') 
               AND YEAR(a.anldat) = 2010 
               AND a.mkm          = 0
             GROUP BY
                   a.schlinh
             HAVING
                   MAX(b.datum3) > MAX(a.datum3)
           )
    ;
    You can see the expected results of the UPDATE statement,
    by modifying the UPDATE statement like this...
    Code:
    /* Remove UPDATE clause
    UPDATE r000400v x 
    */
    SELECT x.*   -- Add select clause
    /* Remove from SET keyword to equol sign 
       SET x.sbeginndat
         = */
         , (SELECT MAX(b.datum3)   -- Add a comma before update value
            ...
           ) AS sbeginndat_new   -- Add column name
     FROM  r000400v x   -- Add FROM clause instead of "UPDATE r000400v x"
     WHERE x.bsstammnr * 100 + x.bsvnr
        IN (SELECT a.schlinh
            ...
           )
    ;

Posting Permissions

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