MySql IF exists, select ELSE insert

Question

I've been suffering this for two hours now. I want to select or insert a record. If the record exists, select its identifier, otherwise insert it and get a new inserted identifier. While I try to run this, but I still get the error message

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); 

Answer 1

In your case, End is missing

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

In any case, your else should return select, the insert will not return anything. if you want to insert, if it doesn’t exist, then return the inserted value (or return if it exists) do the following:

 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 be inserted if it does not exist, and then return. If it already exists, it will not be inserted and returns a value

Change January 2016

The last query will not work on MySql , this is the MSSQL syntax

Answer 2

Without affecting performance and caching, if Domain_ID is an auto_increment field, an easy way to achieve the specific result you want is to use the ON DUPLICATE KEY clause of the INSERT :

 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.

Answer 3

I do not see if

 If Not Exists(Select DomainId From Domains Where Domain = @domain) Begin INSERT INTO Domains(Domain_ID,Domain,Disabled,Description) VALUES(@Domain_ID, @Domain, @Disabled, @Description); End Select @DomainID 

Seeing how you seem to pass it anyway, I really don’t understand why you need this from the request?