ON DUPLICATE KEY UPDATE with Web sql and variables

Question

I am trying to do this but this is not working :

tx.executeSql('INSERT INTO MOVIE (id, rate) VALUES(?,?) ON DUPLICATE KEY UPDATE rate=VALUES(rate)',[result.id,score]);

My id is an INT NOT NULL UNIQUE and rate an INT. I think my syntax is wrong... Do you have a solution ?

Thx :) Anthony.

Answer 1

As stated in the Web SQL Database:

User agents must implement the SQL dialect supported by Sqlite 3.6.19.

So my guess is that you are going to face the same issues you get with Sqlite. It seems that SQLite UPSERT - ON DUPLICATE KEY UPDATE is not supported by Sqlite, so I suggest just trying one of the solutions provided in the answers. Maybe something like this would work:

db.transaction(function (tx) {
    tx.executeSql('INSERT OR IGNORE INTO MOVIE VALUES (?, ?)', [result.id,score]);
    tx.executeSql('UPDATE MOVIE SET rate = ? WHERE id = ?', [score,result.id]);
});

See demo

By the way, Web SQL Database is deprecated.