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.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/0fd30ec6-629e-41ad-82dc-7f9c896ac5cd%40googlegroups.com.
