I am using the mysql driver for node.js "node-mysql" but struggling with the syntax to do a "REPLACE into" and increment a counter. Heres what I have so far, note the "count" field is where i want to increment the curretn table value by 1:
connection.query('REPLACE INTO links SET ?', { symbol: statName, timestamp: now, count: count + 1 }, function(err, result) {
Can anyone advise on how to do this?
Regards, Ben.
Please keep in mind that REPLACE is similar to INSERT not UPDATE.
Under the hood, REPLACE is mechanically a DELETE followed by an INSERT. It says so in the MySQL Documentation as follows:
REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 13.2.5, “INSERT Syntax”.
REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For another MySQL extension to standard SQL—that either inserts or updates—see Section 13.2.5.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.
If you want to increment the count column, use INSERT INTO ... ON DUPLICATE KEY UPDATE.
Please make sure the JavaScript Engine can work with INSERT INTO ... ON DUPLICATE KEY UPDATE. If not, you must capture the count as newcount, increment newcount, and feed the number into the REPLACE
connection.query('REPLACE INTO links SET ?', { symbol: statName, timestamp: now, count: newcount }, function(err, result) {
Give it a Try !!!
I found the solution in using INSERT with ON DUPLICATE KEY UPDATE:
connection.query('INSERT INTO links SET symbol = ?, timestamp = ?, title = ?, url = ? ON DUPLICATE KEY UPDATE count = count + 1', [statName, now, interaction.data.links.title[y], interaction.data.links.url[y]], function(err, results) {
if (err) console.log(err);
});