title: How to update two tables using id from one table to another?
body: <p>I am writing a Node JS script, what I want to use for updating 2 tables from a remote database. The script does the following so far:</p>
  <ul>
  <li>It connects to the remote database, selects the new values since a given time.</li>
  <li>It connects to the local database, inserts or updates the new/edited rows to table A.</li>
  <li>Updates or inserts new rows to the second table using id from table A as primary key.
  I've done task 1 and 2.</li>
  </ul>
  <p>My code so far:</p>
  <pre><code>let mysql = require('mysql');
  
  let con = mysql.createConnection({ //Server A
      host: &quot;192.168.1.10&quot;,
      user: &quot;user1&quot;,
      password: &quot;pswd1&quot;,
      database: &quot;a_database&quot;
  });
  
  let con2 = mysql.createConnection({ //Server B
      host: &quot;192.168.1.11&quot;,
      user: &quot;user2&quot;,
      password: &quot;pswd2&quot;,
      database: &quot;b_database&quot;
  });
  
  let sqlSelectDatabaseA = &quot;SELECT uid,name,email,city,street,number FROM users \n&quot; +
      &quot;WHERE uid IN (SELECT DISTINCT u.uid FROM logs l INNER JOIN users u ON l.id = u.uid ORDER BY uid;&quot;;
  
  let sqlSelectId = &quot;SELECT max(id) AS ID FROM addresses&quot;;
  
  let sqlInsertUsers = &quot;INSERT INTO users (username,email,mobile,city,street,client_number,contactAddress) VALUES ? ON DUPLICATE KEY UPDATE \n&quot; +
  &quot;username=VALUES(username),email=VALUES(email),mobile=VALUES(mobile),city=VALUES(city),street=VALUES(street);&quot;; //I've solved the insert/update in this sql command
  let sqlInsertAddresses = &quot;INSERT INTO addresses (country,city,street,number) VALUES ?&quot;; //Here I would need update too
  
  con.connect(async function(err) {
      if (err) throw err;
      con.query(sqlSelectDatabaseA, function (err, result) {
          if (err) throw err;
          con2.connect(function(err) {
              if (err) throw err;
              con2.query(sqlSelectId, function (err, result2) {
                  if (err) throw err;
                  let addressId = result2[0].ID;
                  let values1 = [];
                  let values2 = [];
                  for (let i = 0; i &lt; result.length; i++) {
                      addressId++;
                      values1[i] = [result[i].city,result[i].street,result[i].number];
                      values2[i] = [result[i].name,result[i].email,result[i].city,result[i].street + ' ' + result[i].number,result[i].uid,++addressId];
                  }
                  con2.query(sqlInsertAddresses, [values1], function (err, result3) {
                      if (err)
                          throw err;
                      console.log(&quot;Number of records inserted/updated in addresses table : &quot; + result3.affectedRows); //Now I only insert the addresses, don't check them if they exist, this would be the task, to achieve a check if it exists or not, if exists, update it, if not, insert it
                      con2.query(sqlInsertUsers, [values2], function (err, result4) {
                          if (err) throw err;
                          console.log(&quot;Number of records inserted/updated in users table: &quot; + result4.affectedRows);
                          con.end();
                          con2.end();
                      });
                  });
  
              });
  
          });
      });
  });
  </code></pre>
  <p>I would like to find a solution for using the value at users.contactAddress as a PRIMARY KEY on the addresses table. If a record with the given primary key exists, it only updates the values, if doesn't, inserts a new record.</p>
  <p>If it's possible, without new sql commands. But if there is no other way, that will do too.</p>
  <p>Example:</p>
  <ul>
  <li>in the logs table a new row appeared, it looks like |logId|userId|</li>
  <li>the script uses this row's userId, connects to database A, gets the new values from the users table</li>
  <li>using the values from database A the script inserts or updates the users table in database B</li>
  <li>depending on if an update or an insert happened, it adds a row to addresses table, or updates the values in the addresses table where the id (PRIMARY KEY) equals to the updates users record's contactAddress field</li>
  </ul>
  <p><a href="https://i.stack.imgur.com/6hG2o.png" rel="nofollow noreferrer">Database structure</a></p>
  <pre><code>CREATE TABLE IF NOT EXISTS usersA (
    `uid` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(255) NOT NULL,
    `email` varchar(255) NOT NULL,
    `mobile` varchar(255) NOT NULL,
    `city` varchar(255) NOT NULL,
    `street` varchar(255) NOT NULL,
    `number` bigint(20) NOT NULL,
    PRIMARY KEY (`uid`)
  );
  CREATE TABLE IF NOT EXISTS logsA (
    `logId` int(11) NOT NULL AUTO_INCREMENT,
    `userId` varchar(255) NOT NULL,
    PRIMARY KEY (`logId`)
  );
  CREATE TABLE IF NOT EXISTS addressesB (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `city` varchar(255) NOT NULL,
    `street` varchar(255) NOT NULL,
    `buildingNumber` varchar(255) NOT NULL, 
    PRIMARY KEY (`id`)
  );
  CREATE TABLE IF NOT EXISTS usersB (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(255) NOT NULL,
    `mobile` varchar(255) NOT NULL,
    `email` varchar(255) NOT NULL,
    `city` varchar(255) NOT NULL,
    `street` varchar(255) NOT NULL,
    `klient_number` bigint(20) NOT NULL,
    `contactAddress` bigint(20) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `klient_number` (`klient_number`)
  );
  INSERT INTO usersA (username,email,mobile,city,street,number) VALUES(&quot;John Smith&quot;, &quot;john.smith@gmail.com&quot;, &quot;0000&quot;, &quot;city&quot;, &quot;street&quot;, 15);
  INSERT INTO usersA (username,email,mobile,city,street,number) VALUES(&quot;Kate Smith&quot;, &quot;kate.smith@gmail.com&quot;, &quot;0000&quot;, &quot;city_updated&quot;, &quot;street1&quot;, 11);
  INSERT INTO usersA (username,email,mobile,city,street,number) VALUES(&quot;Will Smith&quot;, &quot;will.smith@gmail.com&quot;, &quot;0000&quot;, &quot;city2&quot;, &quot;street2&quot;, 6);
  INSERT INTO usersB (username,email,mobile,city,street,klient_number, contactAddress) VALUES(&quot;John Smith&quot;, &quot;john.smith@gmail.com&quot;, &quot;0000&quot;, &quot;city&quot;, &quot;street 15&quot;, 1, 1);
  INSERT INTO usersB (username,email,mobile,city,street,klient_number, contactAddress) VALUES(&quot;Kate Smith&quot;, &quot;kate.smith@gmail.com&quot;, &quot;0000&quot;, &quot;city 1&quot;, &quot;street1 11&quot;, 2, 2);
  INSERT INTO addressesB (city, street, buildingNumber) VALUES (&quot;city&quot;, &quot;street&quot;, &quot;15&quot;);
  INSERT INTO addressesB (city, street, buildingNumber) VALUES (&quot;city 1&quot;, &quot;street1&quot;, &quot;11&quot;);
  INSERT INTO logsA (userId) VALUES (2);
  INSERT INTO logsA (userId) VALUES (3);
  </code></pre>
  <p>Desired result: the script selects users from usersA table, checks users in usersB table. If there is a record with the same klient_number as the PRIMARY KEY in usersA table (so, uid), then it updates, else it inserts. It does the same in addresses table.</p>
  <p>Thank you for your help!</p>