How to INSERT a row if the data doesn't exist or fetch id if it does?

[Priyadarshi Raj]

Hello there!

I'm building a web app to that manages quizzes. I have three tables: Questions, QuestionBanks, and QuestionTypes. Every QuestionBank contains several questions and each question will have a certain type and some content. Below is the sample schema I'm using:

CREATE TABLE IF NOT EXISTS QuestionBanks(
    id INT IDENTITY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS QuestionTypes(
    id INT IDENTITY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS Questions(
    bank_id INT NOT NULL,
    id INT NOT NULL,
    type_id INT NOT NULL,
    content JSON NOT NULL,
    answer JSON NOT NULL,
    PRIMARY KEY (bank_id, id),
    CONSTRAINT fk_bank FOREIGN KEY (bank_id) REFERENCES QuestionBanks(id),
    CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES QuestionTypes(id)
);

I'm having trouble while creating a new QuestionBank row. When I'm inserting new Questions, I want to be able to also create entries for any new QuestionTypes that are encountered. Basically, what I want is:

if (NEW_TYPE is not present in QuestionTypes) {
    insert NEW_TYPE into QuestionTypes
}
type_id = get ID for NEW_TYPE

Then I will use type_id to insert questions into the Questions table.

Please suggest a solution for this.

[Evgenij Ryazanov]

Hello.

What exactly do you want? Do you know the id of QuestionBanks? Or you know only its name and want to lookup the id for the known name of insert a new row with such name?

If so, you can execute something like

WITH S(NAME) AS (VALUES ?)

    SELECT id FROM QuestionBanks JOIN S ON QuestionBanks.name = S.NAME

    UNION SELECT id FROM FINAL TABLE (

        MERGE INTO QuestionBanks T USING S ON T.name = S.NAME WHEN NOT MATCHED THEN INSERT (name) VALUES (S.NAME))

    FETCH FIRST ROW ONLY

but such pure SQL solution will have some overhead. It would be more efficient to execute

SELECT id FROM QuestionBanks WHERE name = ?

separately, and execute the INSERT only when this query didn't return a row.

SELECT id FROM FINAL TABLE (
    INSERT INTO QuestionBanks(name) VALUES (?))

You can also execute the simple INSERT and retrieve the id using getGeneratedKeys().

In any case you need a UNIQUE constraint for the name column.

[Priyadarshi Raj]

My goal is to persist a list of Question POJOs to the database.

This is the Question class:

@Data
public class Question {
    private Long id;
    private String type;
    private String content;
    private String answer;
}

I'm currently using something like this in my prepared statement:

INSERT INTO Questions VALUES
(?, ?, (SELECT id FROM QuestionTypes WHERE name = ?),
 ? FORMAT JSON, ? FORMAT JSON);

The only issue I'm facing is for the third parameter (the inner query for a QuestionType). In case the target type doesn't exist, the operation fails. What I want is: to insert a corresponding row for new QuestionType, and then use its id. Here's how I roughly want it to look:

INSERT INTO Questions VALUES
(?, ?, (SELECT id FROM QuestionTypes WHERE name = ?), // or 'INSERT INTO QuestionTypes' if absent and use the generated id
 ? FORMAT JSON, ? FORMAT JSON);

Is anything like that possible with simple commands? Would you like to suggest another approach?

[Evgenij Ryazanov]

Try

INSERT INTO Questions VALUES (
    ?,
    ?,
    (WITH S(NAME) AS (VALUES ?)
        SELECT id FROM QuestionTypes JOIN S ON QuestionTypes.name = S.NAME

        UNION SELECT id FROM FINAL TABLE

(
            MERGE INTO QuestionTypes T USING S ON T.name = S.NAME WHEN NOT MATCHED THEN INSERT (name) VALUES (S.NAME)
        )
    ),

    ? FORMAT JSON,
    ? FORMAT JSON
);

You need a recent version of H2 for such command, old versions don't support this syntax.

Don't forget to create a constraint to ensure data consistency and to speed up this command:

ALTER TABLE QuestionTypes ADD UNIQUE(name);

[Priyadarshi Raj]

Wow! That works like a charm.

Thanks a lot!

Can you explain what's happening within the WITH clause? I'm still an SQL beginner.

[Evgenij Ryazanov]

There is a UNION between two queries. One of them is a simple lookup, it returns a row only if it exists.

Another one is a query from a data change delta table from the results of inner MERGE command that tries to merge a temporary table with the one row (VALUES ?) into the target table. Because only the WHEN NOT MATCHED clause was used, it will insert the row only if it was not found. This row will be also returned. If such row already exists it will be matched and result will be empty.
https://h2database.com/html/grammar.html#data_change_delta_table

https://h2database.com/html/commands.html#merge_using

So either first or second query return a row.

This command is not thread-safe, if row will be inserted concurrently by another transaction, the row will not be returned. You can swap these two queries for more safety.

    (WITH S(NAME) AS (VALUES ?)

        SELECT id FROM FINAL TABLE(

            MERGE INTO QuestionTypes T USING S ON T.name = S.NAME WHEN NOT MATCHED THEN INSERT (name) VALUES (S.NAME)
        )

        UNION
        SELECT id FROM QuestionTypes JOIN S ON QuestionTypes.name = S.NAME
    )