INSERT IF NOT EXISTS ELSE RETURN THE VALUE-CodePudding

I need an SQLite query which inserts a value into some table if not exist, and return the value if exist. So far I've found "insert if not exists" part here as:

INSERT INTO tableName (str1, str2, date)
SELECT 'example','someText', DATETIME()
WHERE NOT EXISTS (SELECT 1 FROM tableName WHERE str1 = 'example')

I'm new at SQLite but I need something like:

IF NOT EXIST: INSERT the value
ELSE (IF EXIST): RETURN the value

Can somebody tell me how to do this please? Thank you.

CodePudding user response：

For my understanding, SQLite doesn't support this. But in the future it will support this.

See: https://www.sqlite.org/draft/lang_returning.html#:~:text=The RETURNING clause is designed to provide the,report the chosen values back to the application.

It's in a draft to be implemented.

The possible SQL could be:

INSERT INTO tableName (str1, str2, date)
SELECT 'example','someText', DATETIME()
WHERE NOT EXISTS (SELECT 1 FROM tableName WHERE str1 = 'example') RETURNING *;