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.

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