Good day. Tell me please, is it possible to implement this code only in SQL, bypassing PHP? The data table has a field ( email_disp ) that needs to be transferred to another table ( privacy ). The caveat is that the latter already has a user base with the necessary id . There are a lot of answers with INSERT, but it is necessary to update the data, and not to create new records.

$query = DB::use()->query("SELECT id, email_disp FROM data"); while($row = $query->fetch_assoc()) { DB::use()->query('UPDATE privacy SET email_disp = '.$row['email_disp'].' WHERE id = '.$row['id'].'); }
  • one
    What kind of DBMS? MySQL? - Anton Shchyrov
  • It was she. 5.7, if this is important (hardly) - Sid
  • REPLACE INTO to force insertion or replacement where data already existed. INSERT..ON DUPLICATE, which is the same, but with checking for repetitions. dev.mysql.com/doc/refman/5.7/en/replace.html - Lexx918

1 answer 1

Use the INSERT ... ON DUPLICATE KEY UPDATE construct. It will add new entries and update old ones.

 INSERT INTO privacy ( id, email_disp ) SELECT id, email_disp FROM data ON DUPLICATE KEY UPDATE email_disp = VALUES(email_disp);

PS Of course, privacy.id must be a primary or unique key.

  • Thank you, I saved a lot of time - Sid