Does MySQL support "if not exists"?

Question

I have a database where i put some societies and their employees. The thing is that two societies can have an employe with the same name (but they are different person). So when I want to insert an employe (stagiaire) I try to check if it exists for this society. Here is my query :

INSERT INTO stagiaire(Nom,Email,Telephone,IDSociete )
VALUES('Paul','ppk@mail.fr','0000000000','7' )
WHERE(SELECT stagiaire.ID FROM stagiaire 
LEFT JOIN societe ON stagiaire.IDSociete = societe.ID
WHERE stagiaire.Nom = 'Paul' AND societe.NomSoc = 'PachaKebab') NOT EXISTS

Heres 'Paul' already exists in the society 'Promoplantes' and I have another 'Paul' in the society 'PachaKebab' (ID = 7) the one I try to insert.

I tried few things like IF NOT EXISTS the my INSERT query (between begin/end) but it seems like phpMyAdim (SQL Server that i use) can't unserstand the IF NOT EXISTS or NOT EXISTS code.

Is that true ? Or do I have an error in my code that I dont see ?

FYI phpmyadmin is a database administration tool, your database engine is probably MySQL, — JeffUK, Jun 01 '17 at 08:00
Maybe this question can help you https://stackoverflow.com/questions/4707063/how-can-i-do-an-insert-where-not-exists — EstevaoLuis, Jun 01 '17 at 08:00
read the doc. https://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html — Felix, Jun 01 '17 at 08:02
Don't use `not exists` when a simple unique index will do the job. — Phylogenesis, Jun 01 '17 at 08:02
I've never used the world's favourite MySQL client (I prefer desktop clients) so I hope I'm not spreading wrong information but I'm pretty sure that it doesn't use its own SQL syntax. Who would want to learn yet another SQL dialect that's only valid for a single client? — Álvaro González, Jun 01 '17 at 08:31
If you add a unique index to the composite field `(Nom, IDSociete)` then the DB itself will prevent you from entering a name collision and you don't need to use an `if not exists` clause on every attempt to insert. — Phylogenesis, Jun 01 '17 at 08:33
@Phylogenesis That doesn't help when inserting multiple rows in one batch, as the violation of the unique key will abort the entire batch, rather than allowing in those rows which don't conflict. You could do it with an `ON CONFLICT DO NOTHING`, if MySQL supports that, though that's no longer very simple for someone new to SQL. (The query in the original question may only have one row, but tips that don't generalise to similar problems should be given with care.) — IMSoP, Jun 01 '17 at 08:43
@IMSoP If you're inserting many records in a single batch, then the equivalent `if not exists` clause becomes even more ridiculous. Nevertheless, every best-practice is to put restrictions like this into indexes and constraints, so the one time you forget to add logical clauses into your SQL statements does not lead to broken/orphaned data. — Phylogenesis, Jun 01 '17 at 08:56
@Phylogenesis The best practise I've always been exposed to is to have the constraints, but never deliberately rely on them - the application should be avoiding creating duplicate data so that it can handle it more gracefully, rather than having to roll back a transaction when some constraint fires an error. I agree that "not exists" is not the most scalable solution here, but "just use unique constraint and catch the errors" isn't particularly either. — IMSoP, Jun 01 '17 at 09:02

EstevaoLuis · Accepted Answer · 2017-06-01T09:59:39.517

0

The correct syntax is SELECT ... FROM ... WHERE NOT EXISTS

INSERT INTO stagiaire(Nom,Email,Telephone,IDSociete )
SELECT 'Paul','ppk@mail.fr','0000000000','7'
FROM stagiaire
WHERE NOT EXISTS(SELECT stagiaire.ID FROM stagiaire 
LEFT JOIN societe ON stagiaire.IDSociete = societe.ID
WHERE stagiaire.Nom = 'Paul' AND societe.NomSoc = 'PachaKebab')
LIMIT 1;

edited Jun 01 '17 at 09:59

answered Jun 01 '17 at 08:01

EstevaoLuis

2,422
7
33
40

@TheTinyCat Can you post what error message you got? – EstevaoLuis Jun 01 '17 at 08:09
@TheTinyCat So, try adding a LIMIT 1 at the end of the query – EstevaoLuis Jun 01 '17 at 10:00
Okay its works pretty good even if i dont understand how the query works x). Thanks body – TheTinyCat Jun 01 '17 at 10:04

RoMEoMusTDiE · Answer 2 · 2017-06-01T09:49:14.320

0

Use not exists and restructuring your query

   INSERT INTO stagiaire(Nom,Email,Telephone,IDSociete )
    Select Nom,Email,Telephone,IDSociete
    From stagiaire s
    WHERE NOT EXISTS(SELECT 1 from societe x
    WHERE x.id = s.IDSociete)
And s.IDSociete = 7

edited Jun 01 '17 at 09:49

answered Jun 01 '17 at 08:24

RoMEoMusTDiE

4,739
1
17
26

I dont understand your 'SELECT 1 (?) FROM...' – TheTinyCat Jun 01 '17 at 09:47
using not exists or exist will only need at least a match, no need to read all the data – RoMEoMusTDiE Jun 01 '17 at 09:48
Yes but '1' means nothing. Or is it a binary (or boolean) condition ? – TheTinyCat Jun 01 '17 at 09:54
select 1 is just a confirmation that there is a record other will return blank... it's not a boolean or bit. just how we test if there is a match – RoMEoMusTDiE Jun 01 '17 at 09:57
Okay so it works like a boolean conditions. But its not i get it. thanks. – TheTinyCat Jun 01 '17 at 10:05

Does MySQL support "if not exists"?

2 Answers2