How to INSERT / WHERE NOT EXIST in SQL

Question

I'm having problems with the next sentence:

INSERT INTO `pasajeros` 
( 
    `nombre`,
    `apellidos`,
    `email`,
    `password`,
    `telefono`
) 
VALUES 
(
    'Alexandra',
    'Gonzalez',
    '[email protected]',
    '1234567!',
    '04242344556' 
) 
WHERE NOT EXISTS 
( 
    SELECT  * 
    FROM    `transportistas` 
    WHERE   `email` = '[email protected]'
);

What's wrong in my code? I need to make an Insert if a email is not used in another table. That was my approach, but I can't figure it out why my query fails.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE NOT EXISTS ( SELECT * FROM `transportistas` WHERE `email` = '[email protected]' at line 1 — Cesar Jr Rodriguez, Mar 16 '17 at 20:06
http://stackoverflow.com/questions/485039/mysql-insert-where-query — Brad, Mar 16 '17 at 20:08

Brad · Accepted Answer · 2017-03-16T20:19:42.403

3

I don't believe you can use Values with a where clause. But you can insert using a select statement which can handle a where clause just fine.

INSERT INTO `pasajeros` 
( 
    `nombre`,
    `apellidos`,
    `email`,
    `password`,
    `telefono`
) 
Select * from (
select 
     'Alexandra' as `nombre`,
     'Gonzalez' as `apellidos`,
     '[email protected]' as `email`,
     '1234567!' as `password` ,
     '04242344556' as `telefono`
    ) i
WHERE NOT EXISTS 
( 
    SELECT  * 
    FROM    `transportistas` 
    WHERE   `email` = i.email
);

edited Mar 16 '17 at 20:19

answered Mar 16 '17 at 20:05

Brad

11,934
4
45
73

1

"`nombre` = 'Alexandra'" - I dont believe that works. You should get an error like "unknown column `nombre`" – Paul Spiegel Mar 16 '17 at 20:13
@that kind of thing would work in SQL server...and I never really do MySQL. Can you only use `as` to alias? – Brad Mar 16 '17 at 20:17
@PaulSpiegel thats correct Paul. I'm getting these error, how can I fix it? – Cesar Jr Rodriguez Mar 16 '17 at 20:17
@Brad The updated version should work. However - you don't even need any aliases here. – Paul Spiegel Mar 16 '17 at 20:24
@PaulSpiegel, yes I know they are not required but I do like to have them for clarity. – Brad Mar 16 '17 at 20:38

score 2 · Answer 2 · answered Mar 16 '17 at 20:17

2

The correct way to do what you want is to define a unique index/constraint:

create unique index unq_pasajeros_email on pasajeros(email);

The database will then protect the data.

This is much better than trying to do this at the application layer, because it prevents duplication if someone manually updates the data.

answered Mar 16 '17 at 20:17

Gordon Linoff

1,242,037
58
646
786

What means unq_pasajeros_email? The email must to be unique in both `pasajeros` and `transportistas` tables, the field in both cases is called `email` – Cesar Jr Rodriguez Mar 16 '17 at 20:20
@CesarJrRodriguez . . . Then you need two unique indexes, one for each table. However, your question only concerns `pasajeros`. – Gordon Linoff Mar 17 '17 at 02:04

How to INSERT / WHERE NOT EXIST in SQL

2 Answers2