INSERT INTO SELECT FROM ON DUPLICATE KEY UPDATE: 1287 'VALUES function' is deprecated-CodePudding

I've created a query that does what I need but MySQL server (8.0.29) issues 1 warning. I'm wondering if it's possible to get rid of the warning.

Query:

INSERT INTO order_summary (product_id, num_orders)

SELECT product_id, COUNT(product_id) AS num_orders
FROM `order` GROUP BY product_id

ON DUPLICATE KEY UPDATE num_orders=VALUES(num_orders);

Warning:

1287 '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

DB structure:

CREATE TABLE `order` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `order_summary` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_id` int NOT NULL,
  `num_orders` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_id_UNIQUE` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Thanks

CodePudding user response：

INSERT INTO order_summary (product_id, num_orders)
SELECT *
FROM (
  SELECT product_id, COUNT(product_id) AS num_orders
  FROM `order` 
  GROUP BY product_id
  ) data
ON DUPLICATE KEY UPDATE num_orders=data.num_orders;

https://dbfiddle.uk/VZ9vhdAe

PS. If data source does not perform GROUP BY then SELECT * FROM ( .. ) data is excess, and you may refer to source tables columns immediately.