CODEWITHSUNDEEP
sqldb2sql-insertrollback
omkar sirra
omkar sirra

Reputation: 726

Redeployable script in DB2 SQL. Insert a row only If a row doesn't exist

I have seen scripts working for SQL server using If not exists.

But I couldn't execute similar script in DB2.

Below link is just an example but not exactly my requirement

DB2 Drop table if exists equivalent

Upvotes: 1

Views: 1633

Answers (1)

mustaccio
mustaccio

Reputation: 18945

Unfortunately you did not provide your table structure and sample data, so I can only guess what they look like, but, as suggested by data_henrik, you should probably use the MERGE statement, something like:

MERGE INTO target_table tgt
USING TABLE (VALUES ( 42, 'foo', 'bar')) AS src ( id, val1, val2 )
ON tgt.id = src.id
WHEN NOT MATCHED
THEN INSERT ( id, col1, col2 ) VALUES ( src.id, src.val2, src.val1 )

The above assumes that target_table.id is how you determine that "a row doesn't exist". Adjust the condition to include all key columns as necessary.

If your Db2 version, which for some reason you also chose not to disclose, does not yet support the MERGE statement, you can simulate the behaviour using the standard INSERT statement:

INSERT INTO target_table tgt ( id, col1, col2 )
SELECT FROM TABLE (VALUES ( 42, 'foo', 'bar')) AS src ( id, val1, val2 )
WHERE NOT EXISTS (
  SELECT FROM target_table WHERE id = src.id
)

Upvotes: 3

Related Questions