One common way is to apply select query in following format
SELECT 1 FROM TABLE WHERE <matching conditions="">
If this query returns a record then we will apply update statement (assuming the record exist), if not we apply insert statement. This looks really good. But the problem is for each record we need to send two queries either SELECT+INSERT or SELECT+UPDATE.
Yet another approach which I have seen is, they will either INSERT the record without checking. If in case the statement throws an constraint exception, then they will apply the UPDATE command.
This is good idea if the matching conditions fields and table constraint fields are same, it not the insert will not throw any exception :)
While discussing this to one of my friend, he mentioned they first apply the UPDATE command first. If it returns '0' (Zero), then they will apply INSERT command.
This looks like good idea and if all the records are for update they are in good state, but what if all the records are for inserts? The performance is still there.
Today one of my friend, after reading my initial post, he was commenting that we can do the whole thing in a Stored Procedure. Which is also good idea. But my stand was, Stored Procedure is normally used to do a set of operations depending on some parameter, rather than single query execution. Will are planning to have more discussion later and will keep you updated.
Recently during one of my development I came across same scenario. The database which I was using is DB2. I noticed a 'MERGE' query which does INSERT/UPDATE in a same query. Looks interesting, but the MERGE is usually used to combine two different tables. It reads the data from SOURCE table, compare with current table. If match found, depending on the given condition, then they do UPDATE else they INSERT.
But what I was looking for is MERGING on same table. After couple of research finally I end-up with writing a query which does INSERT or UPDATE depending on their existence.
Here for each record we issue only one command.
Following is the query which I used.
MERGE INTO EMPLOYEE as EMP
USING
(((SELECT ID,NAME,AGE,SALARY
FROM CP_WEEKLY_LIFT_FACTOR
WHERE NAME = 'Joe' and AGE = 35)
UNION
(SELECT -1 as ID,'Joe' as NAME,35 as AGE,10000 as SALARY FROM SYSIBM.SYSDUMMY1))
order by ID desc fetch first 1 row only -- This is important espc for Update
) as EMPDATA
ON
EMP.ID = EMPDATA.ID
WHEN MATCHED THEN
UPDATE SET
EMP.SALARY = EMPDATA.SALARY
WHEN NOT MATCHED THEN
INSERT (NAME,AGE,SALARY)
VALUES (EMPDATA.NAME,EMPDATA.AGE,EMPDATA.SALARY)
What I am doing here is,
SELECT A RECORD DEPENDING ON SOME CONDITION
COMBINE THE RESULT WITH ONE MORE SELECT WITH THE VALUES TO INSERT
SELECT THE FIRST RECORD OUT OF IT
IF MATCH THEN update ELSE INSERT
This query is tested and working in DB2 version. I think Oracle also have something same to this.
One problem I noticed when using this is, when using Java Batch Statement use the casting CAST(? AS INTEGER) for parameterized fields.
Hope someone will like this and may use this in their project.