How to do a simple INSERT OR UPDATE?

[jonas.jh...@gmail.com]

I would like to use "insert or update" on a record to either insert a new record if it does not exist or update if it does.

I have following table:

object_id  bigint not null,
language   char(3) not null,
content    text

Primary key is: (object_id, language)

If I use store() on a newly created and filled record, I'll get a violation exception if the primary key already exists. But I can't use update(), since I don't know if this record already exists.

What is the preferred JOOQ way to do this?

[Lukas Eder]

Hello,

You have a couple of options:

Try inserting or updating on error:

try {

    record.insert();

}

catch (DataAccessException e) {

    // Perhaps, check e's cause to be a SQLIntegrityConstraintViolationException

    record.update();

}

Note that if there is an exception in an insert(), update(), or store() operation, the internal changed flags won't be modified. This approach obviously suffers from the usual possibility of creating race conditions if multiple threads try to INSERT / UPDATE the same record.

Running an explicit INSERT .. ON DUPLICATE KEY UPDATE statement:

DSL.using(configuration)

   .insertInto(TABLE)

   .set(record)

   .onDuplicateKeyUpdate()

   .set(record)

   .execute();

Note the set(record) methods are convenience methods to copy all (changed) values from your record into the statement. If you're not using MySQL or MariaDB, the above statement will still work on all of those databases that support the SQL standard MERGE statement.

Hope this helps

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Deven Phillips]

Note the set(record) methods are convenience methods to copy all (changed) values from your record into the statement. If you're not using MySQL or MariaDB, the above statement will still work on all of those databases that support the SQL standard MERGE statement.

IMPRESSIVE! 

[Lukas Eder]

2014-06-24 15:16 GMT+02:00 Deven Phillips <deven.p...@gmail.com>:

Note the set(record) methods are convenience methods to copy all (changed) values from your record into the statement. If you're not using MySQL or MariaDB, the above statement will still work on all of those databases that support the SQL standard MERGE statement.

IMPRESSIVE! 

:-)

Well... No more vendor-lockin with jOOQ... 

[Deven Phillips]

It always annoys me when people say they MUST use MySQL because it has UPSERT... UPSERT is STUPID, but implementing that capability using a SQL standard like MERGE is VERY NICE!

[Lukas Eder]

2014-06-24 18:15 GMT+02:00 Deven Phillips <deven.p...@gmail.com>:

It always annoys me when people say they MUST use MySQL because it has UPSERT...

People do say that?

 

UPSERT is STUPID, but implementing that capability using a SQL standard like MERGE is VERY NICE!

I thought so as well :)

We're also emulating INSERT IGNORE ... using MERGE, in fact. Except that the jOOQ syntax is INSERT .. ON DUPLICATE KEY IGNORE 

[Deven Phillips]

I have had MANY co-workers and colleagues make that statement about MySQL... I think it just indicates laziness... Oh well, perhaps I am overly critical because I am such a huge fan of PostgreSQL..

Deven

[Raman Gupta]

PostgresQL does not support MERGE though (http://wiki.postgresql.org/wiki/SQL_MERGE). So I think if one is using PostgresQL one would generally fall back to the exception/update approach. Is that right?

Regards,

Raman

[Lukas Eder]

The PostgreSQL / MERGE discussion is a heated one for the PostgreSQL developers (from what I know). Being a highly standards-compliant database, it is a bit of an oddity that this powerful statement is not yet supported. Even Derby is about to support it in the next release (joining CUBRID, DB2, Firebird, HSQLDB, Oracle, SQL Server, and Sybase SQL Anywhere):

http://blog.jooq.org/2013/08/16/apache-derby-about-to-adopt-the-awesome-sql2003-merge-statement/

In the mean time, you can probably tweak your statement semantics to perform a single-statement MERGE through:

    INSERT INTO table (...)

    SELECT ...

    FROM (

        SELECT ...

        FROM merge_source

        WHERE NOT EXISTS (

            UPDATE table

            SET ...

            WHERE ...

            RETURNING

        )

    )

The above is pseudo-PostgreSQL. You'll have to experiment a little to see how you can get it working. Maybe there is a formal way to transform SQL MERGE into PostgreSQL INSERT .. SELECT .. NOT EXISTS .. UPDATE?

Note that PostgreSQL probably doesn't know the MERGE semantics of this statement, so race conditions might occur. In general, you're probably better off with the insert / on exception update approach.

Cheers

Lukas

--