Results 1 to 14 of 14
  1. #1
    Join Date
    May 2016
    Posts
    9

    Answered: Merge into query

    Hi All
    I am very new to DB2 and need help with the following functionality:
    TGT table
    Col1,Col2,Col3,Col4,Col5,Col6


    SRC table
    SCol1,SCol2,SCol3,SCol4,SCol5
    A,D,2,5,E
    A,D,2,10,W
    A,D,2,15,R

    I have to merge rows from SRC to TGT table. Unique index on TGT table is on Col1,Col2,COl3
    TGT table should look like this after merge
    Col1,Col2,Col3,Col4,Col5,Col6
    A,D,2,5,10,15

    my merge query looks like this
    merge into TGT using (select SCol1,SCol2,SCol3,SCol4,SCol5 from SRC table) SRC ON (TGTCol1=SRC.SCol1 and TGT.Col2=SRC.SCol2 and TGT.Col3=SRC.SCol3)
    WHEN MATCHED THEN UPDATE
    SET TGT.Col4=DECODE(SRC.SCol5,'E',SRC.SCol4,null), TGT.Col5=DECODE(SRC.SCol5,'W',SRC.SCol4,null),TGT. Col6=DECODE(SRC.SCol5,'R',SRC.SCol4,null)
    WHEN NOT MATCHED THEN INSERT (TGT.Col1,TGT.Col2,TGT.Col3,TGT.Col4,TGT.Col5,TGT. Col6)
    VALUES (SRC.SCol1,SRC.SCol2,SRC.SCol3,DECODE(SRC.SCol5,'E ',SRC.SCol4,null),DECODE(SRC.SCol5,'W',SRC.SCol4,n ull),DECODE(SRC.SCol5,'R',SRC.SCol4,null))


    But, when i run this, i get an error message saying the unique index on table TGT is violated.
    What am i doing wrong?

  2. Best Answer
    Posted by mark.b

    "Try this:

    Code:
    merge into TGT using 
    (
      select SCol1,SCol2,SCol3
    , max(DECODE(SRC.SCol5, 'E', SRC.SCol4, null)) SCol4
    , max(DECODE(SRC.SCol5, 'W', SRC.SCol4, null)) SCol5
    , max(DECODE(SRC.SCol5, 'R', SRC.SCol4, null)) SCol6
    from SRC 
    group by SCol1, SCol2, SCol3
    ) SRC
    ON (TGT.Col1=SRC.SCol1 and TGT.Col2=SRC.SCol2 and TGT.Col3=SRC.SCol3)
    WHEN MATCHED THEN UPDATE SET Col4=SRC.SCol4, Col5=SRC.SCol5, Col6=SRC.SCol6
    WHEN NOT MATCHED THEN INSERT (Col1, Col2, Col3, Col4, Col5, Col6)
    VALUES (SRC.SCol1, SRC.SCol2, SRC.SCol3, SRC.SCol4, SRC.SCol5, SRC.SCol6)
    ;
    "


  3. #2
    Join Date
    Jan 2003
    Posts
    4,286
    Provided Answers: 5
    Please post DDL for both tables including all indexes.

    Andy

  4. #3
    Join Date
    May 2016
    Posts
    9
    CREATE TABLE TGT (
    Col1 varchar(1) NOT NULL,
    Col2 varchar(1) NOT NULL,
    Col3 varchar(1) NOT NULL,
    Col4 decimal(19,4) NULL,
    Col4 decimal(19,4) NULL,
    Col5 decimal(19,4) NULL
    );


    CREATE UNIQUE INDEX TGT_IDX
    ON TGT(Col1,Col2,Col3);

    CREATE TABLE SRC (
    SCol1 varchar(1) NOT NULL,
    SCol2 varchar(1) NOT NULL,
    SCol3 varchar(1) NOT NULL,
    SCol4 decimal(19,4) NULL,
    SCol5 varchar(1) NOT NULL
    );

    CREATE UNIQUE INDEX SRC_IDX
    ON SRC(SCol1,SCol2,SCol3,SCol5);

  5. #4
    Join Date
    Jan 2003
    Posts
    4,286
    Provided Answers: 5
    What is the entire error message you are getting?

    Andy

  6. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    Try this:
    merge into TGT using (select * from(select SCol1,SCol2,SCol3,SCol4,SCol5, rownumber() over(partition by scol1, scol2, scol3) rn_ from SRC table) where rn_=1) SRC
    ...

    The reason is that you may have duplicates in SRC by non-existing (scol1, scol2, scol3) values in TGT.
    Last edited by mark.b; 05-17-16 at 13:03.
    Regards,
    Mark.

  7. #6
    Join Date
    May 2016
    Posts
    9
    DB2 SQL error: SQLCODE: -803, SQLSTATE: 23505, SQLERRMC: 3;TGTTABLE
    Message: One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "3" constrains table "TGTTABLE" from having duplicate values for the index key.

  8. #7
    Join Date
    May 2016
    Posts
    9
    Quote Originally Posted by mark.b View Post
    Hi,

    Try this:
    merge into TGT using (select distinct SCol1,SCol2,SCol3,SCol4,SCol5 from SRC table) SRC
    ...

    This dint work either. getting the same error
    DB2 SQL error: SQLCODE: -803, SQLSTATE: 23505, SQLERRMC: 3;TGTTABLE
    Message: One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "3" constrains table "TGTTABLE" from having duplicate values for the index key. 

  9. #8
    Join Date
    Jan 2003
    Posts
    4,286
    Provided Answers: 5
    Quote Originally Posted by mark.b View Post
    Hi,

    Try this:
    merge into TGT using (select distinct SCol1,SCol2,SCol3,SCol4,SCol5 from SRC table) SRC
    ...
    I forgot about duplicate rows being inserted at the same time. This still will not work. Only SCol1,SCol2,SCol3 cannot be duplicated in the select statement. If you have something like:

    SCol2 SCol2 SCol3 SCol4 Scol5
    A B C 10.1 X
    A B C 10.1 Z

    The Select would still return both rows, and the duplicate error will occur again.

    You need to remove the duplicates in the Select part of the merge.

    Andy

  10. #9
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    You caught my initially incorrect version.
    Try the newer one...
    Regards,
    Mark.

  11. #10
    Join Date
    May 2016
    Posts
    9
    Quote Originally Posted by mark.b View Post
    Hi,

    Try this:
    merge into TGT using (select * from(select SCol1,SCol2,SCol3,SCol4,SCol5, rownumber() over(partition by scol1, scol2, scol3) rn_ from SRC table) where rn_=1) SRC
    ...

    The reason is that you may have duplicates in SRC by non-existing (scol1, scol2, scol3) values in TGT.
    I tried this, the query ran without any error but i dint get the desired output
    now, tgt table just reads
    Col1,Col2,Col3,Col4,Col5,Col6
    A,D,2,5

    Col5, COl6 are not updated. IS there anythign wrong with the way DECODE is used?

  12. #11
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Try this:

    Code:
    merge into TGT using 
    (
      select SCol1,SCol2,SCol3
    , max(DECODE(SRC.SCol5, 'E', SRC.SCol4, null)) SCol4
    , max(DECODE(SRC.SCol5, 'W', SRC.SCol4, null)) SCol5
    , max(DECODE(SRC.SCol5, 'R', SRC.SCol4, null)) SCol6
    from SRC 
    group by SCol1, SCol2, SCol3
    ) SRC
    ON (TGT.Col1=SRC.SCol1 and TGT.Col2=SRC.SCol2 and TGT.Col3=SRC.SCol3)
    WHEN MATCHED THEN UPDATE SET Col4=SRC.SCol4, Col5=SRC.SCol5, Col6=SRC.SCol6
    WHEN NOT MATCHED THEN INSERT (Col1, Col2, Col3, Col4, Col5, Col6)
    VALUES (SRC.SCol1, SRC.SCol2, SRC.SCol3, SRC.SCol4, SRC.SCol5, SRC.SCol6)
    ;
    Regards,
    Mark.

  13. #12
    Join Date
    May 2016
    Posts
    9
    Quote Originally Posted by mark.b View Post
    Try this:

    Code:
    merge into TGT using 
    (
      select SCol1,SCol2,SCol3
    , max(DECODE(SRC.SCol5, 'E', SRC.SCol4, null)) SCol4
    , max(DECODE(SRC.SCol5, 'W', SRC.SCol4, null)) SCol5
    , max(DECODE(SRC.SCol5, 'R', SRC.SCol4, null)) SCol6
    from SRC 
    group by SCol1, SCol2, SCol3
    ) SRC
    ON (TGT.Col1=SRC.SCol1 and TGT.Col2=SRC.SCol2 and TGT.Col3=SRC.SCol3)
    WHEN MATCHED THEN UPDATE SET Col4=SRC.SCol4, Col5=SRC.SCol5, Col6=SRC.SCol6
    WHEN NOT MATCHED THEN INSERT (Col1, Col2, Col3, Col4, Col5, Col6)
    VALUES (SRC.SCol1, SRC.SCol2, SRC.SCol3, SRC.SCol4, SRC.SCol5, SRC.SCol6)
    ;
    Thanks!
    This worked.

  14. #13
    Join Date
    May 2016
    Posts
    9
    Quote Originally Posted by mark.b View Post
    Try this:

    Code:
    merge into TGT using 
    (
      select SCol1,SCol2,SCol3
    , max(DECODE(SRC.SCol5, 'E', SRC.SCol4, null)) SCol4
    , max(DECODE(SRC.SCol5, 'W', SRC.SCol4, null)) SCol5
    , max(DECODE(SRC.SCol5, 'R', SRC.SCol4, null)) SCol6
    from SRC 
    group by SCol1, SCol2, SCol3
    ) SRC
    ON (TGT.Col1=SRC.SCol1 and TGT.Col2=SRC.SCol2 and TGT.Col3=SRC.SCol3)
    WHEN MATCHED THEN UPDATE SET Col4=SRC.SCol4, Col5=SRC.SCol5, Col6=SRC.SCol6
    WHEN NOT MATCHED THEN INSERT (Col1, Col2, Col3, Col4, Col5, Col6)
    VALUES (SRC.SCol1, SRC.SCol2, SRC.SCol3, SRC.SCol4, SRC.SCol5, SRC.SCol6)
    ;
    Hi
    My actual query is in such a way that in few places i have to reference the TGT columns in the decode statements
    Eg.DECODE(SRC.SCol5, 'E', SRC.SCol4, Col4)
    In that case, how do i modify this? I tried using it this way
    select SCol1,SCol2,SCol3
    , max(DECODE(SRC.SCol5, 'E', SRC.SCol4, TGT.Col4)) SCol4
    , max(DECODE(SRC.SCol5, 'W', SRC.SCol4, null)) SCol5
    , max(DECODE(SRC.SCol5, 'R', SRC.SCol4, null)) SCol6

    but i get an error saying that TGT.Col4 is not a valid name

  15. #14
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    You are not allowed to reference a target table's column in the subselect of the USING clause.
    Use the following:
    WHEN MATCHED THEN UPDATE SET Col4=coalesce(SRC.SCol4, TGT.Col4)
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

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