UPSERT

UPSERT stands for UPDATE or INSERT in a single operation. In certain cases a record with a same unique key (not necessarily primary!), may already exist in the table. This is where UPSERT comes to the rescue. SQL:2003 standard defines MERGE clause to handle it, but, unfortunately, not all vendors support it. Yet, the most popular provide some way to perform an UPSERT.Below are the examples for Oracle, SQL Server, PostgreSQL, MySQL.

Entity used:

@Tuple
@Table(name = "member_topic")
@Data // lombok
public static class MemberTopic {
    @Id
    private int Member;
    @Id
    private int topic;
    private String notes;
}

Oracle

Supports MERGE

MemberTopic newTopic = ... // external parameter

FluentQuery query = FluentJPA.SQL((MemberTopic target) -> {

    // view is used for INSERT and alias
    View<MemberTopic> targetView = viewOf(target, MemberTopic::getMember,
                                                  MemberTopic::getTopic,
                                                  MemberTopic::getNotes);
    // source is a sub query
    MemberTopic source = subQuery(() -> {
        // select and alias values in the view order
        SELECT(targetView.fromAliased(newTopic));
        FROM(DUAL()); // The Oracle way
    });

    // match new record with existing
    MERGE().INTO(target).USING(source)
            // matching criteria
            .ON(target.getMember() == source.getMember()
             && target.getTopic() == source.getTopic());

    WHEN_MATCHED().THEN(() -> {
        MERGE_UPDATE().SET(() -> {
            // just update the notes
            target.setNotes(source.getNotes());
        });
    });

    // perform the INSERT
    WHEN_NOT_MATCHED().THEN(MERGE_INSERT(targetView.columnNames(),
                                         VALUES(targetView.from(source))));
});

query.createQuery(em).executeUpdate();

SQL Server

Supports MERGE

MemberTopic newTopic = ... // external parameter

FluentQuery query = FluentJPA.SQL((MemberTopic target) -> {

    // view is used for INSERT and alias
    View<MemberTopic> targetView = viewOf(target, MemberTopic::getMember,
                                                  MemberTopic::getTopic,
                                                  MemberTopic::getNotes);
    // source is a sub query
    MemberTopic source = subQuery(() -> {
        // select and alias values in the view order
        SELECT(targetView.fromAliased(newTopic));
    });

    // match new record with existing
    MERGE().INTO(target).USING(source)
            // matching criteria
            .ON(target.getMember() == source.getMember()
             && target.getTopic() == source.getTopic());

    WHEN_MATCHED().THEN(() -> {
        MERGE_UPDATE().SET(() -> {
            // just update the notes
            target.setNotes(source.getNotes());
        });
    });

    // perform the INSERT
    WHEN_NOT_MATCHED().THEN(MERGE_INSERT(targetView.columnNames(),
                                         VALUES(targetView.from(source))));
});

query.createQuery(em).executeUpdate();

PostgreSQL

No MERGE support

int newMember = 0;
int newTopic 110;
String newNotes = "test";

FluentQuery query = FluentJPA.SQL((MemberTopic target) -> {
    INSERT().INTO(target);
    VALUES(row(newMember, newTopic, newNotes));
    ON_DUPLICATE_KEY_UPDATE(() -> {
         target.setNotes(newNotes);
    });
});

query.createQuery(em).executeUpdate();

MySQL

No MERGE support

int newMember = 0;
int newTopic 110;
String newNotes = "test";

FluentQuery query = FluentJPA.SQL((MemberTopic target) -> {
    INSERT().INTO(target);
    VALUES(row(newMember, newTopic, newNotes));
    ON_CONFLICT(MemberTopic::getMember, MemberTopic::getTopic).DO_UPDATE().SET(() -> {
         target.setNotes(newNotes);
    });
});

query.createQuery(em).executeUpdate();