zakiovi zakiovi - 6 months ago 10
MySQL Question

Update With SELECT FROM Table GROUP BY

I have table material :

- id: int,
- name : varchar(100),
- content: varchar(255),
- quantity : double,


the query without using group by

1, product1, content1, 25
2, product2, content2, 4
3, product1, content3, 35
4, product3, content4, 15


the query with group by and SUM quantity

product1, content1, 25
product2, content2, 4
product3, content4, 15


when I want to update a quantity = 0 of the first id: 1, product1, content1, 25, I would make an update of the quantity = 0 for all materials that have the same name of the material
For example:

1, product1, content1, 0
2, product2, content2, 4
3, product1, content3, 0
4, product3, content4, 15


I tried with :

Update 'material' as m
INNER JOIN (SELECT i1.produit FROM 'material' m1 GROUP BY m1.name) m1
ON(m1.name = m.name) SET quantity = 0


but I have not the result :

Answer

Try following;)

UPDATE `material` AS m  
INNER JOIN (
    SELECT m1.name
    FROM `material` m1
    GROUP BY m1.name
    HAVING COUNT(m1.name) > 1) m2   
ON m2.name = m.name
SET m.quantity = 0
Comments