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

Why query for count(*) ? just query for domain match, if there will be non-empty result, return it else insert data. — Zaffy, Oct 01 '12 at 15:32
I'm still getting the hang of MySQL syntax (so I won't post this as an answer), but I believe you want something along the lines of `IF EXISTS (SELECT 1 FROM Domains WHERE Domain = @domain)`... instead of `SELECT CASE WHEN (SELECT COUNT(*) FROM Domains WHERE Domain = @domain)>0` — LittleBobbyTables - Au Revoir, Oct 01 '12 at 15:33
I did try if else, if exists and stuff like that.. I ended up with the code you see and I keep getting an error on the insert.. I'm howping I get an answer because I don't know anything about MySql — kms, Oct 01 '12 at 16:40
For anyone else that is suffering with something like this. Because I don't know much of MySql I desided to go with simple querys insert, select, update, delete and do my logic programmaticaly. I will need to make a lot of requests to the data base and since I'm using ASP.NET I hope it won't be too slow.. — kms, Oct 02 '12 at 07:29

Gonzalo.- · Answer 1 · 2017-01-02T14:00:21.163

6

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

edited Jan 02 '17 at 14:00

answered Oct 01 '12 at 15:33

Gonzalo.-

12,512
5
50
82

AFAIK, `CASE` should also have a `THEN` right before `END` if that makes sense. – redej Oct 02 '12 at 13:47
1

MySQL does not support `INTO ... WHERE`? http://stackoverflow.com/questions/485039/mysql-insert-where-query – user1267259 Jan 02 '17 at 02:57

score 1 · Answer 2 · answered Dec 18 '13 at 15:08

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.

MySql IF exists select ELSE insert

2 Answers2

Linked