sqlite3 - insertOrUpdate multiple rows by key array

Question

I got three tables (person, language, personSpeaksLanguage) and I want to insertOrUpdate the link table (personSpeaksLanguage) by a given id (personId) and array of keys (languageIds). How to archive this?

Example:

person
| id | name    |
|  1 | 'Maria' |
|  2 | 'Jim'   |
|  4 | 'John'  |

language
| id | name      |
|  1 | 'english' |
|  2 | 'spanish' |
|  4 | 'japanse' |

personSpeaksLanguage
| personId | languageId | deleted |
|    1     |     2      |    0    |
|    4     |     2      |    1    |
|    4     |     3      |    0    |
|    2     |     1      |    1    |


Example vars: 
  database='./database.db'  
  table='personSpeaksLanguage' 
  personId=2 
  languageIds=[1,2,4]

The aim is to achieve that for personSpeaksLanguage the values (2,2) and (2,4) get added. Since (2,1) is already in the table. But got softdeleted previously, it should update to deleted = 0. This is my pitifully try without update.

const sqlite3 = require('sqlite3').verbose();

export default function insertOrUpdateMultiple(database, table, personId, languageIds) {
  return new Promise((resolve, reject) => {
    const db = new sqlite3.Database(database);
    let i = 0;
    while (i < languageIds.length) {
      db.run(`INSERT INTO ${table} (id, name) VALUES (${personId}, ${languageIds[i]})`, values, (err) => {
        if (err) reject(err);
      }, (err) => {
        if (err) {
          reject(err);
        } else {
          i += 1;
        }
      });
    }
  });
}

score 0 · Accepted Answer · answered Nov 05 '17 at 13:25

With help of this I got:

INSERT INTO mytable (col1, col2, col3) VALUES
    (1, 2, "abc"),
    (2, 4, "xyz"),
    (3, 5, "aaa"),
    (4, 7, "bbb");

I combined the languageIds to pairs ((${personId}, ${languageIds[i]}) and joined them.

const sqlite3 = require('sqlite3').verbose();

export default function insertOrUpdateMultiple(database, personId, languageIds, values) {
  const vals = [];
  for (let i = 0; i < languageIds.length; i += 1) {
    vals.push(`(${personId}, ${languageIds[i]})`);
  }
  const valStr = vals.join(', ');

  return new Promise((resolve, reject) => {
    const db = new sqlite3.Database(database);
    db.run(`INSERT INTO ${table} (personId, languageId) VALUES ${valStr}`, (err) => {
      if (err) reject(err);
    }, (err) => {
      if (err) {
        reject(err);
      } else {
        resolve();
      }
    });
  });
}

sqlite3 - insertOrUpdate multiple rows by key array

1 Answers1