Geek Answers Handbook

Trying to escape -803 in a DB2 insert

I am trying to insert a single row that may or may not exist. I would rather not select it first and / or get -803 if it exists. I did some research and tried both to ignore and the merge operator, but keep getting syntax errors for both. In any case, I am not trying to copy data from another table - so merging is not very suitable.
Is there any possibility in DB2 SQL to just issue a failover insert and not need to code it? In other words, is there any insertion syntax that ensures that data will be added if it does not exist or will return a null status even if it does?

+5
source share
4 answers

In short, the answer is no. In the long run, it all depends on the limitations that you have set up on your desk. If you try to insert against a table that has a unique restriction on the column and the data already exists that you are trying to insert, you will get an error in DB2 (and any other DBMS).

In this case, the best option is probably to write a stored procedure that checks if the record exists before creating it.

+2
source

MERGE is suitable since you can dynamically send values ​​in the USING clause (see example 5 at http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2. udb.admin.doc / doc / r0010873.htm ).

MERGE - DB2 V8.2! , , V8.1, ?

, :

SELECT
if (found) UPDATE else INSERT

concurrency: , , . , SELECT WITH RR USE AND KEEP UPDATE LOCKS.

+7

update, 0 , insert.

+2

sysibm.sysdummy( dual Oracle DB2). , , , 0, . AFAIK, , .

. , , , .

insert into table (column1, column2, column3, column4, column5)
select 'A', 'B', 'C', 'D', 'E' from dual where not exists (select * from table where column1 = 'A');

I found this very useful for cases where I have several processes that are inserted into the table, and I can not guarantee the order of these inserts. You can determine if the insert was successful or not with a return value that will be 1 if it was successful, or 0 if not

+2
source

More articles: