How to make a query WHERE NOT EXIST after an INSERT in SQL


I have problems with the following query:

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 do I have wrong with the code? I need to make an Insert only if the email is not used in that other table. Just add the WHERE NOT EXISTS sentence to the side but apparently I do not comply with the required syntax, how can I improve my code?

asked by Cesar Jr Rodriguez 16.03.2017 в 21:04

4 answers


To use the where not exists , you need a select . So you need to convert the clause values to select :

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

Another way, following the same principle, a little more complicated, but that prevents you from repeating the value for the email in 2 places, is using a derived table:

INSERT INTO 'pasajeros' ( 'nombre','apellidos','email','password','telefono')
SELECT i.nombre, i.apellidos,, i.password, i.telefono
  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' t WHERE t.'email' =;
answered by 16.03.2017 / 21:27

Another idea could be with a condition like this:

    (   SELECT  1
        FROM    'transportistas'
        WHERE   'email'= '[email protected]'
        INSERT 'pasajeros'('nombre','apellidos','email','password','telefono') 
        VALUES ('Alexandra','Gonzalez','[email protected]','1234567!','04242344556') 
answered by 16.03.2017 в 22:05

You can also do it with JOIN :

INSERT INTO 'pasajeros' 
   SELECT 'Alexandra','Gonzalez',,'1234567!','04242344556'
           FROM (SELECT '[email protected]' AS email) temp
           LEFT JOIN transportistas ON =
           WHERE IS NULL

A temporary table is created with the value of the email and compared with the email column of carriers, if it is NULL, the insertion is made because said email does not exist in carriers.

answered by 16.03.2017 в 22:17

Dear, I did my work as a researcher and I have found a way in which I could achieve its goal, hopefully it will help. Greetings

INSERT INTO 'pasajeros'('nombre','apellidos','email','password','telefono')
SELECT '[email protected]'
FROM dual
WHERE NOT EXISTS (SELECT 'email' FROM 'pasajeros' WHERE 'email' = '[email protected]')LIMIT 1
answered by 16.03.2017 в 21:31