CODEWITHSUNDEEP
javasqlinsertrequest
Mercer
Mercer

Reputation: 9986

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

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.

Upvotes: 1

Views: 110

Answers (4)

barbalion
barbalion

Reputation: 175

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.

Upvotes: 2

Arion
Arion

Reputation: 31249

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;

Upvotes: 1

Chad
Chad

Reputation: 7517

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.

Upvotes: 3

Greg
Greg

Reputation: 2627

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.

Upvotes: 1

Related Questions