Reputation: 219
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
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
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