Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    48

    Smile Unanswered: Is there a way to insert nonduplicate rows while duplicates getting dropped

    Hi,

    I am trying to insert into a table by selecting certain columns from other table. The first table does contain some rows. The select produces some rows which are already there in first table and also produces some new rows. These new rows are what I want to insert into first table.

    I am using an sql like

    Insert into table1
    select colA,colB,colC from table2

    I get sql -803 as the table 1 is not empty and there is a unique index defined.

    Is there a way to insert only the non duplicates from 'select colA,colB,colC from table2' ?

    Thanks
    Mahendra

  2. #2
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    hi.

    you're defined fk in some column?

  3. #3
    Join Date
    Apr 2004
    Posts
    48
    No but I do have a unique primary key in table1 and that prevents the dups.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Insert into table1 a
    select colA,colB,colC from table2 b
    where not exists (select a.colA from table1 where a.colA = b.colA)

    This assumes that the primary key is colA.

    I am not 100% sure this works on an "insert into select from" statement, or if it does work, which version is DB2 is required (maybe on 8.2 only or does not work at all on any version). But the subselect is valid syntax for pure select statement.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    no primary key, foreing key in the destination table? or a column with not null?

Posting Permissions

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