How to combine UPDATE and COUNT in a query
-
Hi, Could someone please help me in this simple task. I have two tables below. I need to set checked="ok" for those products that have no entry in [expiration date] table. Thanks in advance. I imagine this would be something like this, but it's incorrect:
UPDATE (product as p INNER JOIN [expiration date] AS ed ON p.id=ed.product_id) SET checked='ok' WHERE (COUNT(*) FROM [expiration date] = 0) product ------- id name checked expiration date --------------- product_id exp_date quantity -
Hi, Could someone please help me in this simple task. I have two tables below. I need to set checked="ok" for those products that have no entry in [expiration date] table. Thanks in advance. I imagine this would be something like this, but it's incorrect:
UPDATE (product as p INNER JOIN [expiration date] AS ed ON p.id=ed.product_id) SET checked='ok' WHERE (COUNT(*) FROM [expiration date] = 0) product ------- id name checked expiration date --------------- product_id exp_date quantityHope this helps U. Test this with some fake table before U proceed. update product set ckecked='ok' where id not in (select product_id from expirationdate) Regards, Arun Kumar.A -- modified at 10:43 Sunday 22nd April, 2007
-
Hi, Could someone please help me in this simple task. I have two tables below. I need to set checked="ok" for those products that have no entry in [expiration date] table. Thanks in advance. I imagine this would be something like this, but it's incorrect:
UPDATE (product as p INNER JOIN [expiration date] AS ed ON p.id=ed.product_id) SET checked='ok' WHERE (COUNT(*) FROM [expiration date] = 0) product ------- id name checked expiration date --------------- product_id exp_date quantityTry this: update p set checked='ok' from product p where id in ( select id from product p2 join expirationdate e on p2.id = e.product_id group by id having count(*) = 0) I guess the question would be if there are records or not, it might be easier to do a left join if there are no records. So that would be: update p set checked='ok' from product p where id in ( select id from product p2 left join expirationdate e on p2.id = e.product_id where e.product_id is null) Hope that helps. Ben