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. — user823738
– user823738, Commented Oct 1, 2012 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
– LittleBobbyTables - Au Revoir, Commented Oct 1, 2012 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
– kms, Commented Oct 1, 2012 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
– kms, Commented Oct 2, 2012 at 7:29

Gonzalo.- · Accepted Answer · 2017-01-02 14:00:21Z

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 2, 2017 at 14:00

answered Oct 1, 2012 at 15:33

Gonzalo.-

12.7k5 gold badges53 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

redej Over a year ago

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

user1267259 Over a year ago

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

ravi · Accepted Answer · 2013-12-18 15:08:58Z

1

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.

answered Dec 18, 2013 at 15:08

ravi

3,3894 gold badges21 silver badges11 bronze badges

Collectives™ on Stack Overflow

MySql IF exists select ELSE insert

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related