3

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 ?

5 Answers 5

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

4
  • 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." May 13, 2015 at 15:53
  • @Jehad Keriaki Thanks man it's working now INSERT IGNORE INTO users` SET nUserId='$entity_id', vLoginName='$name', vFirstName='$firstname', vLastName='$lastname', vEmail='$email'` May 13, 2015 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. May 13, 2015 at 16:02
  • If you don't believe me, read this answer by Bill Karwin: “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE” May 13, 2015 at 16:05
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')
1
  • The solution.
    – Déjà vu
    Sep 19, 2018 at 3:11
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 ;
1
  • Good solution: easy to understand and read
    – Julian
    Sep 12, 2016 at 9:44
1

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.

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);
}
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, 2019 at 6:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.