Kosta
Kosta

Reputation: 219

MySQL update from nested SELECT query

I have 2 tables which are getting different values from different sources. One table called stock has 2 fields, code and stock, while other have code, stock (and bunch more not relevant to what I want to do) Basically my current query for checking if there are enough quantities is following

SELECT * 
FROM products
WHERE products.code 
NOT IN (SELECT stock.code from stock) 
AND products.stock > 0 
AND products.product_active = 1

Which works, but I would like to extend it to UPDATE stock field of the table products to value 0 for the results of the above query.

Thanks

Upvotes: 2

Views: 2366

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

UPDATE products p
LEFT JOIN stock s ON p.code = s.code
SET p.stock = 0
WHERE p.stock > 0 AND p.product_active = 1 AND s.code IS NULL

Upvotes: 3

sagi
sagi

Reputation: 40481

So why don't you do it? Its very similar to what you just did :

UPDATE products p
SET p.stock = 0
WHERE p.code NOT IN(SELECT s.code FROM stock s)
  AND p.stock > 0
  AND p.product_Active = 1

I don't like to use IN() statement, since it doesn't handle NULL values very well, you can also use NOT EXISTS() instead of it:

UPDATE products p
SET p.stock = 0
WHERE NOT EXISTS(SELECT 1 FROM stock s 
                 WHERE p.code = s.code)
  AND p.stock > 0
  AND p.product_Active = 1

Upvotes: 2

Related Questions