Update the value when the row already exists

Question

I have table 'A' in schema 'USER'. There are three columns in this table. below are the names of the columns:

• id (primary key)
• name
• age

I want to insert a row in this table, but if the row is already present (with same id) then the name and age should be updated instead of giving error sqlcode -803.

Is there any way to do this in a single query?

Answer 1

As mentioned in comments on the question, MERGE is the answer you are looking for.

Check out this great blog as well:

MERGE statement explained by Serge Rielau (瑞赛奇) and Rick Swagerman.

It will look somewhat like this for your case:

MERGE INTO <tab> AS ...
  WHEN MATCHED THEN
     UPDATE ...
  WHEN NOT MATCHED THEN
     INSERT...