2

I have been trying to convert a complex SELECT query to an UPDATE query, but I keep getting the 1064 syntax error.

The goal for the query is to update certain rows which meet particular conditions, however the joins and the GROUP BY and HAVING statements are making this impossible with the query I have now. I know this isn't the right way, but I can't discover what the solution should be. I would be great if somebody could tell me a direction for a solution!

My query (the values are not correct but give some more context):

UPDATE products p
JOIN products_to_specifications pts ON pts.products_id = p.products_id
JOIN products_specifications ps ON ps.specifications_id = pts.specifications_id
SET p.products_image = 'file_name.jpg'
WHERE ps.specifications_name IN ('color') 
AND pts.content IN ('black')
AND p.products_manufacturer = 'BMW'
AND p.products_name = 'M5'
GROUP BY p.products_id
HAVING COUNT(DISTINCT ps.specifications_name) = 1 AND COUNT(DISTINCT pts.content) = 1

My table structure:

table products: products_id, products_image, etc. (basic info same for every product)

table products_specifications: specifications_id, specifications_name

table products_to_specifications: products_id, specifications_id, content

I think for the right solution I will have use a subquery, but I'm not sure how to structure the query

Ruben
  • 89
  • 7

1 Answers1

1

MySql do not support GROUP BY in UPDATE statement. You can easily update by making the complex query as a derived table as below.

UPDATE products p, (SELECT p.products_id FROM products p
JOIN products_to_specifications pts ON pts.products_id = p.products_id
JOIN products_specifications ps ON ps.specifications_id = pts.specifications_id
WHERE ps.specifications_name IN ('color') 
AND pts.content IN ('black')
AND p.products_manufacturer = 'BMW'
AND p.products_name = 'M5'
GROUP BY p.products_id
HAVING COUNT(DISTINCT ps.specifications_name) = 1 AND COUNT(DISTINCT pts.content) = 1) AS t
SET p.products_image = 'file_name.jpg' WHERE p.products_id=t.products_id

Hope this helps you.

Suraj
  • 451
  • 7
  • 17
  • Even I can't see the whole code ... I think there is a typo! you need to replace p. with t. in the whole SELECT (otherwise you cant access it in the last WHERE via t.products_id) – Tobias Gaertner Sep 13 '17 at 07:16