How to write a query that can be run several times, but only insert once?

Question

I have this request

INSERT INTO SERVICEPAYANT_CLIENT (RE_ID, TYPE_DONNEES)
SELECT CLIENT_ID, 160 AS TYPE_DONNEES
FROM    REFERENTIEL r, CLIENT_APPLICATIF ca
WHERE r.ID = ca.ID_REFERENTIEL
AND r.TYPE=1
GROUP BY CLIENT_ID
HAVING COUNT(*)>0;

When I execute it several times I inserted my data several times but I would like my request I inserted my data once only. I would like to insert my data if they are not present in my table.

http://stackoverflow.com/q/108403/284240 – Tim Schmelter Apr 26 '12 at 14:11 — Tim Schmelter, Apr 26 '12 at 14:11

score 3 · Accepted Answer · answered Apr 26 '12 at 14:11

You can do:

INSERT INTO SERVICEPAYANT_CLIENT (RE_ID, TYPE_DONNEES)
SELECT CLIENT_ID, 160 AS TYPE_DONNEES
FROM    REFERENTIEL r, CLIENT_APPLICATIF ca
WHERE r.ID = ca.ID_REFERENTIEL
AND r.TYPE=1
AND NOT EXISTS (
    SELECT * FROM SERVICEPAYANT_CLIENT sp
    WHERE sp.RE_ID = CLIENT_ID AND TYPE_DONNEES = 160)
GROUP BY CLIENT_ID
HAVING COUNT(*)>0;

The "AND NOT EXISTS..." is what restricts it to returning rows that are not already in your target table.

barbalion · Answer 2 · 2012-04-26T14:31:28.673

There is a sentence MERGE in several mature DBMS for these cases. MERGE is INSERT and UPDATE at same time.

General syntax example:

     MERGE INTO TABLE_NAME USING table_reference ON (condition)
       WHEN MATCHED THEN
       UPDATE SET column1 = value1 [, column2 = value2 ...]
       WHEN NOT MATCHED THEN
       INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...

Some DBMS like MySQL has its own syntax with the same idea. See more in Wikipedia.

If your DBMS doesn't soppurt that then you can write a Stored Procedure with the same logic.

But a stored procedure or any kind of other check in your code before insertion will make a 'lag' and the operation will become 'non-atomic'. It means that it is possible that just after your check and before insertions another transaction can make a duplicate record and you will get unexpected duplicates or excpetions. To avoid this you will have to lock the table before this operation to obtain exclusive access to the table what has a porfomance penalty because of serialization of the access.

Or you can use INSERT with SELECT like this:

    INSERT (field1, ...) INTO table1
    SELECT value1, ...
    FROM DUAL -- put a name of analogue of Oracle's DUAL here
    WHERE NOT EXISTS (
       SELECT 1 
       FROM table1
       WHERE key = :new_key
    )

But as you understand it will not update anything.

score 1 · Answer 3 · answered Apr 26 '12 at 14:10

1

The typical solution would be to enforce it in the database with a unique constraint comprised of the fields that define a record. Your Java code would then get an error back from the database letting you know that it already exists.

answered Apr 26 '12 at 14:10

Greg

2,476
4
22
28

1

+1. I was going to propose to add an "if exists" clause before running that SQL script, but it'd be prone to syntactical errors depending on the database and more importantly, it _shouldn't_ be inserted more than once anyway and it's the database's job to enforce that, not the programmer. – Neil Apr 26 '12 at 14:12
It really depends on your application. If you have control of the database, then adding a constraint is a good encapsulation of your rules. But if your transaction volume is massive and you expect that the majority of your records already exist, then it will be much more efficient to handle it in SQL. – Greg Apr 26 '12 at 14:19
1

True, but the point is that this _shouldn't_ happen, and should be treated as an exception, not the rule. Therefore, either the programmer catches constraint exceptions or better still, he avoids multiple calls himself and you get both efficiency and correctness. – Neil Apr 26 '12 at 14:28

score 1 · Answer 4 · answered Apr 26 '12 at 14:12

You could do this:

INSERT INTO SERVICEPAYANT_CLIENT (RE_ID, TYPE_DONNEES)
SELECT CLIENT_ID, 160 AS TYPE_DONNEES
REFERENTIEL r
JOIN CLIENT_APPLICATIF ca
    ON r.ID = ca.ID_REFERENTIEL
WHERE 
    r.TYPE=1
    AND NOT EXISTS
        (
            SELECT
                NULL
            FROM
                SERVICEPAYANT_CLIENT
            WHERE
                REFERENTIEL.CLIENT_ID=SERVICEPAYANT_CLIENT.RE_ID
        )
GROUP BY CLIENT_ID
HAVING COUNT(*)>0;

How to write a query that can be run several times, but only insert once?

4 Answers4