Search code examples
sqliteinsert-select

INSERT INTO...SELECT...WHERE with null if condition fails

I'm attempting to do something like the following:

INSERT INTO `File` (`Name`, `Owner`) 
SELECT @File, `Users`.Id
FROM `Users`
WHERE `Users`.`Name` = @UserName

However, if there is no match in the Users table, I would like it to still insert the file but with a null value. So like performing a LEFT JOIN on the Users table with ON replacing the WHERE. But I can't just use a LEFT JOIN because I don't have a table to join it to. Can anyone help me out in how I could do this without breaking it into two separate queries?

This is specifically for SQLite.

Solution

  • Maybe something like this could do the trick:

    INSERT INTO `File` (`Name`, `Owner`) 
SELECT @File, `Users`.Id
FROM (SELECT @UserName as Name) notable
left join `Users` on `Users`.`Name` = notable.Name