Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Location
    Ottawa, Canada
    Posts
    58

    Unhappy Unanswered: Unsure of db2 functionality for INSERT/UPDATE

    I know I've posted a similiar question before, but I'm really running out of ideas for this problem.....and really need some help on it

    I'm trying to update a row in a table, but if its there already just update it. I've recently come from mySQL where i could do an UPDATE, then an INSERT IGNORE which would update the row if its there then insert if its not.

    I've tried using merge, but in many cases the source table does not make a match.

    I've tried doing an UPDATE then INSERT ... SELECT, that works but in the case that the table is empty it never inserts...

    EXAMPLE:

    -- update the row if its there
    UPDATE CargoManifestParty SET (Message_Id,Ordinal,Entity_Identifier_Code,Name,Ad dress_Line_One) = ('test@DUMMY',0,'SH','TEST','BOX 31') WHERE Message_Id = 'test@DUMMY' AND Ordinal = 0 AND Entity_Identifier_Code = 'SH'

    -- insert the row if its not there...this won't work if the table is empty
    INSERT INTO CargoManifestParty SELECT 'test@DUMMY',0,'SH','TEST','BOX 31' FROM CargoManifestParty WHERE NOT EXISTS(SELECT 1 FROM CargoManifestParty WHERE Message_Id = 'test@DUMMY' AND Ordinal=0 AND Entity_Identifier_Code = 'SH') FETCH FIRST ROW ONLY

    The reason i'm inserting/updating is because i'm added data in batches and the data will contain rows that exist and some that don't.

    I'm fairly new to db2 syntax, so any help will be much appreciated.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You do not mention which OS or DB2 version you are running. With DB2 8.1 FOR LUW FP2 (I believe) and later, DB2 provides the MERGE INTO statement that will perform the Insert/Update that you want. Look int the SQL Reference on how to use it.

    Andy

Posting Permissions

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