HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMinor

Insert if NOT EXISTS not working

Submitted by: @import:stackexchange-dba··
0
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

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.