CODEWITHSUNDEEP
mysqlsqlsql-updatecase
Asad kamran
Asad kamran

Reputation: 440

mysql conditional field update

I have 3 columns in CATEGORY TABLE for storing pre-calculated counts of records for it in another table PRODUCTS.

CATEGORY(c_id,name,c30,c31,c32)

c30=count for New Products (value 30) c31 count for used products (value 31) c32 count for Damaged products (value 32)

PRODUCT(p_id,c_id,name,condition)

condition can be 30,31 or 32.

I am thinking to write a single UPDATE statement so, it will update respective category count.

Althogh below statement is syntactically wrong, but i am looking for similar type of solution.

select case product.condition 
when 30 then update category set category.c30=category.c30+1 where category.c_id=product.category3
when 31 then update category set category.c31=category.c31+1 where category.c_id=product.category3
when 32 then update category set category.c32=category.c32+1 where category.c_id=product.category3
end case
from product 
where product.c_id=12

Any suggestion!

Upvotes: 1

Views: 530

Answers (2)

Preethi
Preethi

Reputation: 120

You can join both the tables and then update the value in same join query.

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79939

You can do this:

UPDATE CATEGORY c
INNER JOIN
(
    SELECT 
      c_id,
      SUM(CASE WHEN `condition` = 30 THEN 1 ELSE 0 END) c30,
      SUM(CASE WHEN `condition` = 31 THEN 1 ELSE 0 END) c31,
      SUM(CASE WHEN `condition` = 32 THEN 1 ELSE 0 END) c32
    FROM product
    GROUP BY c_id
) p ON c.c_id = p.c_id
SET c.c30 = p.c30, 
    c.c31 = p.c31,
    c.c32 = p.c32;

SQL Fiddle Demo

Upvotes: 2

Related Questions