Insert if NOT EXISTS not working

Question

I am trying to copy data from table to another mysql table from two different databases
First I get all data from first table and stored them into php variable
then I loop on that var to insert data to the other table
using this query

INSERT INTO `users` (`nUserId`, `vLoginName`, `vFirstName`, `vLastName`, `vEmail`)
    VALUES ('$entity_id','$name','$firstname','$lastname','$email') 
    WHERE NOT EXISTS (SELECT `nUserId`,`vEmail`
                      FROM `users`
                      WHERE `nUserId`='$entity_id' 
                      AND   `vEmail` = '$email')

but it's not working

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXISTS (SELECT `nUserId`,`vEmail` FROM users '

What the correct syntax to make this work ?

score 2 · Answer 1 · edited May 13 '15 at 15:46

2

I think the problem might be VALUES and I would write the statement as the following:

INSERT INTO `users` 
    (`nUserId`, `vLoginName`, `vFirstName`, `vLastName`, `vEmail`)
SELECT 
    '$entity_id', '$name', '$firstname', '$lastname', '$email' 
FROM dual
WHERE NOT EXISTS (SELECT *
                    FROM `users`
                    WHERE `nUserId`='$entity_id' 
                    AND   `vEmail` = '$email')

edited May 13 '15 at 15:46

ypercubeᵀᴹ

97,895
13
214
305

answered May 13 '15 at 15:44

SQLHound

261
2
14

The solution. – Déjà vu Sep 19 '18 at 03:11

score 2 · Answer 2 · answered May 13 '15 at 16:17

2

you can try something like

delimiter $$
create procedure select_or_insert()
begin
  IF EXISTS (select * from users where username = 'something') THEN
    update users set id= 'some' where username = 'something';
 ELSE 
insert into users (username) values ('something');
  END IF;
end $$
delimiter ;

answered May 13 '15 at 16:17

Bill N. Varelli

687
1
6
18

Good solution: easy to understand and read – Julian Sep 12 '16 at 09:44

score 1 · Accepted Answer · answered May 13 '15 at 15:35

1

You cannot use WHERE clause in an INSERT statement. However, you may use INSERT IGNORE if in your targer table you define nUserId or vEmail as unique keys.

More info about INSERT syntax: https://dev.mysql.com/doc/refman/5.6/en/insert.html

answered May 13 '15 at 15:35

Jehad Keriaki

3,081
1
15
15

1

INSERT IGNORE can also have unwanted side effects in other cases (values not good for the columns, etc.) It may not be the best choice. In your link: "Data conversions that would trigger errors abort the statement if IGNORE is not specified. With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort." – ypercubeᵀᴹ May 13 '15 at 15:53
@Jehad Keriaki Thanks man it's working now INSERT IGNORE INTOusersSETnUserId='$entity_id',vLoginName='$name',vFirstName='$firstname',vLastName='$lastname',vEmail='$email' – Ashraf Hefny May 13 '15 at 15:59
1

As an advice, read my comment above and if you care for your data don't use INSERT IGNORE. Either use INSERT ... ON DUPLICATE KEY UPDATE or the query by @SQLHound. – ypercubeᵀᴹ May 13 '15 at 16:02
If you don't believe me, read this answer by Bill Karwin: “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE” – ypercubeᵀᴹ May 13 '15 at 16:05

score 1 · Answer 4 · answered Jan 15 '16 at 13:25

No need to store them in php.

Creates a table excactly like the one specified, Note: copies only columns

CREATE TABLE tbl_name LIKE db_name.tbl_name;

If you want specific column names:

CREATE TABLE tbl_name SELECT column_name, column_name FROM db_name.tbl_name;

Then if you want to copy all the data do

INSERT INTO tbl_name SELECT * FROM db_name.tbl_name;

Or specific column data do

INSERT INTO tbl_name SELECT column_name, column_name FROM db_name.tbl_name;

If you want specific data add a WHERE clause.

score 0 · Answer 5 · edited Apr 16 '19 at 06:31

0

$existeEmail=$mysqli->query("SELECT * FROM tb_users WHERE email = '$email'");
 if(!$existeEmail=mysqli_fetch_array($existeEmail))/*si existe en la tabla tblreseteopass*/
{



}else{
    $email = $existeEmail['email'];
      // Direccionado hacia el registro con la Alerta 001
            // ya existe una cuenta asociada a este email, si no recuerda la contraseña, solicite una nueva
            header("Location: https://www.yourpage.com/b/?m=f_lg);
}

edited Apr 16 '19 at 06:31

Glorfindel

2,201
5
17
26

answered Apr 16 '19 at 03:46

jorge mario saldarriaga r.

1

Hola Jorge. Acqui, we normally write in English! If you can't do that, then use Google translate or similar to get your Spanish into English. p.s. Benvenido en el forum! :-) – Vérace Apr 16 '19 at 06:24

Insert if NOT EXISTS not working

5 Answers5

Linked