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?

score 3 · Accepted Answer · edited Mar 16 '16 at 06:49

3

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...

edited Mar 16 '16 at 06:49

Paul White

94,921
30
437
687

answered Mar 02 '16 at 16:06

MichaelTiefenbacher

498
3
6

Update the value when the row already exists

1 Answers1