Q: 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'


A: 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.
