MySQL の INSERT ON DUPLICATE KEY UPDATE

INSERT INTO TABLE_NAME (COLUMN_LIST) VALUES (VALUE_LIST) AS ROW_ALIAS
ON DUPLICATE KEY UPDATE COLUMN_NAME = ROW_ALIAS.COLUMN_NAME;

INSERT INTO rating_count (rating, films) VALUES ('G', 178);

Query OK, 1 row affected (0.00 sec)

INSERT INTO rating_count (rating, films) VALUES ('G', 999);

ERROR 1062 (23000): Duplicate entry 'G' for key 'rating_count.PRIMARY'

INSERT INTO rating_count (rating, films) VALUES ('G', 179) AS new
ON DUPLICATE KEY UPDATE films = new.films;

Query OK, 2 rows affected (0.00 sec)

SELECT * FROM rating_count;

+--------+-------+
| rating | films |
+--------+-------+
| G      |   179 |
+--------+-------+
1 row in set (0.00 sec)

INSERT INTO rating_count (rating, films) VALUES ('G', 179) AS new
ON DUPLICATE KEY UPDATE films = new.films;

Query OK, 0 rows affected (0.00 sec)

INSERT INTO rating_count (rating, films) VALUES ('R', 195) AS new
ON DUPLICATE KEY UPDATE films = new.films;

Query OK, 1 row affected (0.00 sec)

SELECT * FROM rating_count ORDER BY rating;

+--------+-------+
| rating | films |
+--------+-------+
| G      |   179 |
| R      |   195 |
+--------+-------+
2 rows in set (0.00 sec)

INSERT INTO rating_count (rating, films)
SELECT rating, COUNT(*) FROM sakila.film GROUP BY rating
AS new ON DUPLICATE KEY UPDATE films = new.films;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS new ON DUPLICATE KEY UPDATE films = new.films' at line 3

INSERT INTO rating_count (rating, films)
SELECT r, c FROM (SELECT rating AS r, COUNT(*) AS c FROM sakila.film GROUP BY rating) AS new
ON DUPLICATE KEY UPDATE films = new.c;

Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 1  Warnings: 0

SELECT * FROM rating_count ORDER BY rating;

+--------+-------+
| rating | films |
+--------+-------+
| G      |   178 |
| NC-17  |   210 |
| PG     |   194 |
| PG-13  |   223 |
| R      |   195 |
+--------+-------+
5 rows in set (0.00 sec)

INSERT INTO rating_count (rating, films) VALUES ('G', 178)
ON DUPLICATE KEY UPDATE films = VALUES(films);
SHOW WARNINGS\G

Query OK, 0 rows affected, 1 warning (0.00 sec)

*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
1 row in set (0.00 sec)