debugsqlMinor
Insert if NOT EXISTS not working
Viewed 0 times
workingnotinsertexists
Problem
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
but it's not working
What the correct syntax to make this work ?
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 ?
Solution
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')Code Snippets
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')Context
StackExchange Database Administrators Q#101413, answer score: 2
Revisions (0)
No revisions yet.