CODEWITHSUNDEEP
mysqlwhere-clause
Hans Lernestål
Hans Lernestål

Reputation: 85

mySQL UPDATE with WHERE

I am trying to update p.product_status to 0 when two criteria are met.

UPDATE product p JOIN product_to_store p2s ON p.product_id = p2s.product_id
SET p.status=0 
WHERE p.quantity=0 AND p2s.store_id=2

If the p.product_id is in p2s and assigned to to p2s.store_id 2 it should not update p.status to 0.

product_id | store_id
         1 |        0
         1 |        2
         2 |        0
         3 |        0
         4 |        2

Let's say I have the above data in product_to_store. Product Quantity (p.quantity=0) for all.

Alt 1. Product_id=1 and 4: The SQL should NOT update p.status=0 as product_id 1 also is assigned to both store_id 0 and 2.

Alt 2. Product_id=2 and 3: The SQL should update p.status=0 as product_id 2 is only assigned to store_id 0 (and not 2).

Upvotes: 1

Views: 468

Answers (2)

symcbean
symcbean

Reputation: 48387

You don't need a JOIN, this should give the desired result:

UPDATE product p 
SET p.status=0
WHERE p.quantity=0
AND p.product_id IN (
  SELECT ps2.product_id
  FROM product_to_store p2s
  WHERE p2s.store_id=2
)

Although this may not be the most efficient solution depending on the number of records in each table, matching the predicates and represented in indexes.

update

To accomodate the redefined question:

UPDATE product p 
SET p.status=0
WHERE p.quantity=0
AND p.product_id IN (
  SELECT ps2.product_id
  FROM product_to_store p2s_r
  LEFT JOIN product_to_store p2s_2
  ON p2s_r.product_id=p2s_2.product_id
  AND p2s_2.store_id=2
  WHERE p2s_r.store_id<>2
  AND p2s_r.product_id IS NOT NULL
  AND p2s_2.product_id IS NULL
)

Upvotes: 2

fancyPants
fancyPants

Reputation: 51938

This query only updates products which are assigned to either store 0 or store 2.

UPDATE product p
JOIN (
  SELECT product_id 
  FROM product_to_store p2s
  WHERE store_id IN (0, 2) /*not clear, if this is a requirement*/
  GROUP BY product_id 
  HAVING SUM(store_id IN (0, 2)) < 2 OR COUNT(*) = 1
) p2s USING (product_id)
SET p.status = 0
WHERE p.quantity = 0;

The store_id IN (0, 2) in the SUM() function returns true or false, 1 or 0. Therefore we check if the number of rows where this statement is true is lower than 2. So it's either in this store or the other, not in both.

Upvotes: 2

Related Questions