MySql IF exists select ELSE insert

Question

I have been suffering at this for two hours now. I want to select or insert a record. If the record exist select his ID else insert it and get the new inserted ID. For now I'm trying to run this but I still get an error

SELECT CASE WHEN (SELECT COUNT(*) FROM Domains WHERE Domain = @domain)>0 
THEN 
    (SELECT Domain_ID FROM Domains WHERE Domain = @domain) 
ELSE        
    INSERT INTO Domains(Domain_ID,Domain,Disabled,Description) VALUES(@Domain_ID,@Domain,@Disabled,@Description);

Solution 1

Your case is missing an End

Case when (something) then (Some)
else (another thing) end

Anyway, your else must return a select, an insert won't return anything. if you want to insert if not exist and then return the value inserted (or return if exists) do this:

INSERT INTO Domains(Domain_ID,Domain,Disabled,Description) VALUES(@Domain_ID,@Domain,@Disabled,@Description) where not exists (select 1 from Domains WHERE Domain = @domain);

SELECT Domain_ID FROM Domains WHERE Domain = @domain

This will insert if not exists, and then return. If already exists, won't insert, and return the value

Edit Jan 2016

The last query won't work on MySql, it is a MSSQL syntax

Solution 2

Without getting into the impact on performance and caching, if Domain_ID is an auto_increment field, a simple way to achieve the specific result you want is to use the ON DUPLICATE KEY clause of the INSERT statement:

INSERT INTO Domains
    (fld1, fld2, fld3)
    VALUES(....)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);

See the tail section of the ON DUPLICATE KEY UPDATE MySQL documentation.

Comments

[redej]

AFAIK, CASE should also have a THEN right before END if that makes sense.

[user1267259]

MySQL does not support INTO ... WHERE? stackoverflow.com/questions/485039/mysql-insert-where-query