CODEWITHSUNDEEP
mysql
Preston Connors
Preston Connors

Reputation: 407

INSERT INTO...ON DUPLICATE KEY UPDATE Increment Syntax

I'm trying to increment a value from one table to another but I can't figure out the correct syntax. Can someone help me form a correct statement? Thank you in advance:

Current statement:

INSERT INTO points_1_day (nick, amount) 
(SELECT nick, SUM(amount) as increment 
FROM points_log 
WHERE dt >= NOW()-INTERVAL 1 day GROUP BY nick) 
ON DUPLICATE KEY UPDATE 
points_1_day.amount=points_1_day.amount+points_log.increment;

I'm getting this error message:

ERROR 1054 (42S22): Unknown column 'points_log.increment' in 'field list'

Upvotes: 2

Views: 2302

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562270

The expressions in the ON UPDATE clause cannot refer to columns from the SELECT.

But they can refer to VALUES(amount) to get the value that you tried to insert on a given row.

INSERT INTO points_1_day (nick, amount) 
(SELECT nick, SUM(amount) as increment 
FROM points_log 
WHERE dt >= NOW()-INTERVAL 1 day GROUP BY nick) 
ON DUPLICATE KEY UPDATE 
points_1_day.amount=points_1_day.amount+VALUES(amount);

Let me test this and see if it works...

mysql> insert into points_1_day values (123, 10);
Query OK, 1 row affected (0.01 sec)

mysql> insert into points_log (nick, amount, dt) values (123, 15, NOW());
Query OK, 1 row affected (0.13 sec)

mysql> select * from points_1_day;
+------+--------+
| nick | amount |
+------+--------+
|  123 |     10 |
+------+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO points_1_day (nick, amount)  (SELECT nick, SUM(amount) as increment  
    FROM points_log  WHERE dt >= NOW()-INTERVAL 1 day GROUP BY nick)  
    ON DUPLICATE KEY UPDATE  points_1_day.amount=points_1_day.amount+values(amount);
Query OK, 2 rows affected (0.02 sec)
Records: 1  Duplicates: 1  Warnings: 0

mysql> select * from points_1_day;
+------+--------+
| nick | amount |
+------+--------+
|  123 |     25 |
+------+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO points_1_day (nick, amount)  (SELECT nick, SUM(amount) as increment  
    FROM points_log  WHERE dt >= NOW()-INTERVAL 1 day GROUP BY nick)  
    ON DUPLICATE KEY UPDATE  points_1_day.amount=points_1_day.amount+values(amount);
Query OK, 2 rows affected (0.00 sec)
Records: 1  Duplicates: 1  Warnings: 0

mysql> select * from points_1_day;
+------+--------+
| nick | amount |
+------+--------+
|  123 |     40 |
+------+--------+

Yes, it seems to work.

Explanation: http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html says:

You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in INSERT ... UPDATE statements and returns NULL otherwise. Example:

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

This works with INSERT...SELECT too. But the argument to VALUES() is the column name you're inserting into, not the name of the corresponding column from the SELECT.

Upvotes: 4

Related Questions