Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2011
    Posts
    16

    Unanswered: Update using joins

    Please provide the syntax to update using joins.
    I tried many syntax but errors were there.
    I also tried MERGE but it also did not work.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Can you post what SQL you have used and what error you get?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2011
    Posts
    16
    MERGE INTO mbid.TSCHCLD F
    USING mbid.TSCH A
    ON F.FLIGHT_NUMBER = A.FLIGHT_NUMBER AND F.FIRST_LEG_DEP_DATE = A.FIRST_LEG_DEP_DATE
    AND F.FLIGHT_DESIGNATOR = A.FLIGHT_DESIGNATOR AND F.FLIGHT_OP_SUFFIX = A.FLIGHT_OP_SUFFIX AND
    F.FLIGHT_NUMBER = 24
    AND F.FLIGHT_DESIGNATOR = 'DD'
    WHEN MATCHED THEN UPDATE SET F.DEI_2 = 'AA';

    The Error is-
    SQL State = 21506 SQL Code = -788 SQL Message = The same row of target table "MBID.TMSTSCHCLD" was identified more than once for an update, delete or insert operation of the MERGE statement. Exception message = com.ibm.db2.jcc.c.SqlException: The same row of target table "MBID.TMSTSCHCLD" was identified more than once for an update, delete or insert operation of the MERGE statement.
    Last edited by pratikp.vasani; 07-08-11 at 09:59. Reason: NA

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Your join condition should be such that for each row selected to be updated in F , there should be only one row in A. As per this error, there is more than one row.

    Either you have missed a filter condition or it could be a design flaw or it could be constraint error that has allowed duplicate rows to mbid.TSCH.

    If none of these is true, do
    Code:
    MERGE INTO mbid.TSCHCLD F
    USING (select on mbid.TSCH to return only distinct rows for each fltno,deldate,design,suffix) a 
    ON F.FLIGHT_NUMBER = A.FLIGHT_NUMBER AND F.FIRST_LEG_DEP_DATE = A.FIRST_LEG_DEP_DATE
    AND F.FLIGHT_DESIGNATOR = A.FLIGHT_DESIGNATOR AND F.FLIGHT_OP_SUFFIX = A.FLIGHT_OP_SUFFIX AND 
    F.FLIGHT_NUMBER = 24 
    AND F.FLIGHT_DESIGNATOR = 'DD' 
    WHEN MATCHED THEN UPDATE SET F.DEI_2 = 'AA';



    You can find the offending row using
    Code:
    Select F.FLIGHT_NUMBER, F.FIRST_LEG_DEP_DATE, F.FLIGHT_DESIGNATOR,count(*) from mbid.TSCHCLD F, mbid.TSCH A 
    Where  
    F.FLIGHT_NUMBER = A.FLIGHT_NUMBER AND F.FIRST_LEG_DEP_DATE = A.FIRST_LEG_DEP_DATE
    AND F.FLIGHT_DESIGNATOR = A.FLIGHT_DESIGNATOR AND F.FLIGHT_OP_SUFFIX = A.FLIGHT_OP_SUFFIX AND 
    F.FLIGHT_NUMBER = 24 
    AND F.FLIGHT_DESIGNATOR = 'DD' 
    FYI, The above query was done on notepad and not checked for syntax or output.

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jul 2011
    Posts
    16
    Instead of MERGE, can't we use simple JOINT query.
    Also in the Where clause I will be having the conditions which will update multiple rows together.
    Last edited by pratikp.vasani; 07-11-11 at 03:10.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You have not used column value(s) of A to update F.DEI_2.

    So, try
    Example (a):
    Code:
    UPDATE mbid.TSCHCLD F
       SET F.DEI_2 = 'AA'
     WHERE EXISTS
           (SELECT 0
             FROM  mbid.TSCH A
             WHERE F.FLIGHT_NUMBER      = A.FLIGHT_NUMBER
               AND F.FIRST_LEG_DEP_DATE = A.FIRST_LEG_DEP_DATE
               AND F.FLIGHT_DESIGNATOR  = A.FLIGHT_DESIGNATOR
               AND F.FLIGHT_OP_SUFFIX   = A.FLIGHT_OP_SUFFIX
               AND F.FLIGHT_NUMBER      = 24 
               AND F.FLIGHT_DESIGNATOR  = 'DD'
           )
    or
    Example (b):
    Code:
    UPDATE mbid.TSCHCLD F
       SET F.DEI_2 = 'AA'
     WHERE EXISTS
           (SELECT 0
             FROM  mbid.TSCH A
             WHERE F.FLIGHT_NUMBER      = A.FLIGHT_NUMBER
               AND F.FIRST_LEG_DEP_DATE = A.FIRST_LEG_DEP_DATE
               AND F.FLIGHT_DESIGNATOR  = A.FLIGHT_DESIGNATOR
               AND F.FLIGHT_OP_SUFFIX   = A.FLIGHT_OP_SUFFIX
           )
       AND F.FLIGHT_NUMBER      = 24 
       AND F.FLIGHT_DESIGNATOR  = 'DD'
    You will get same result by (a) and (b).
    And if DB2 optimizer was clever enough, it might choose same access path.

  7. #7
    Join Date
    Jul 2011
    Posts
    16
    Hi,
    In the above query I am using one more condition that A.ACTION_IDEN <> 'CNL', but it is updating that row also. And the column ACTION_IDEN is not present in the second table.
    So is it possible with the above situation.
    Last edited by pratikp.vasani; 07-11-11 at 04:27.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    In the above query I am using one more condition that A.ACTION_IDEN <> 'CNL', but it is updating that row also.
    I couldn't understand well your requirements and issue.
    If you include the condition(A.ACTION_IDEN <> 'CNL'),
    SQL wouldn't update the rows having corresponding row(s) with A.ACTION_IDEN = 'CNL'.
    One possibility I could guess was there were multiple corresponding rows in A
    but some were A.ACTION_IDEN = 'CNL' and others were A.ACTION_IDEN <> 'CNL'.

    And the column ACTION_IDEN is not present in the second table.
    No problem.

    If you want to exclude the rows which have corresponding row(s) in A but all A.ACTION_IDEN = 'CNL',
    simply add the condition, like...
    Code:
    UPDATE mbid.TSCHCLD F
       SET F.DEI_2 = 'AA'
     WHERE EXISTS
           (SELECT 0
             FROM  mbid.TSCH A
             WHERE F.FLIGHT_NUMBER      = A.FLIGHT_NUMBER
               AND F.FIRST_LEG_DEP_DATE = A.FIRST_LEG_DEP_DATE
               AND F.FLIGHT_DESIGNATOR  = A.FLIGHT_DESIGNATOR
               AND F.FLIGHT_OP_SUFFIX   = A.FLIGHT_OP_SUFFIX
               AND A.ACTION_IDEN        <> 'CNL'
           )
       AND F.FLIGHT_NUMBER      = 24 
       AND F.FLIGHT_DESIGNATOR  = 'DD'
    ;

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If your requirements were
    Update F if there were matched rows in A,
    but not update if some of matched row(s) were A.ACTION_IDEN = 'CNL'.
    In other words:
    Update F if there were matched rows in A and all of the matched rows were A.ACTION_IDEN <> 'CNL'.
    try...
    Code:
    UPDATE mbid.TSCHCLD F
       SET F.DEI_2 = 'AA'
     WHERE EXISTS
           (SELECT 0
             FROM  mbid.TSCH A
             WHERE F.FLIGHT_NUMBER      = A.FLIGHT_NUMBER
               AND F.FIRST_LEG_DEP_DATE = A.FIRST_LEG_DEP_DATE
               AND F.FLIGHT_DESIGNATOR  = A.FLIGHT_DESIGNATOR
               AND F.FLIGHT_OP_SUFFIX   = A.FLIGHT_OP_SUFFIX
             HAVING
                   SUM(CASE A.ACTION_IDEN
                       WHEN 'CNL' THEN 1
                       ELSE            0
                       END
                      ) = 0
           )
       AND F.FLIGHT_NUMBER      = 24 
       AND F.FLIGHT_DESIGNATOR  = 'DD'
    ;
    Note: If your DB2 supports LISTAGG aggregate function, HAVING clause can be replaced by
    HAVING LOCATE( 'CNL:' , LISTAGG(a.action_iden || ':') ) = 0
    or
    HAVING LISTAGG(a.action_iden || ':') NOT LIKE '%CNL:%'
    Last edited by tonkuma; 07-11-11 at 23:25. Reason: Add Note.

Posting Permissions

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