Reputation: 123
There are two tables:
orders
____________________
order_id | Stat
--------------------
1 | waiting
2 | waiting
3 | waiting
second table:
product
____________________
order_id | product_id
---------------------
1 | 53
2 | 54
3 | 54
order_id value is the same in both tables. I would like to update Stat from 'waiting' to 'done' in Orders table if product_id is '54' in Product table.
I tried this code but didn't work:
mysql_query("UPDATE orders SET stat='done' FROM product WHERE product_id='54'");
will appreciate your help :)
Upvotes: 0
Views: 2063
Reputation: 1644
If you want this logic to be embedded in the DB you could use an update trigger like the one beneath (pseudo code):
CREATE TRIGGER upd_check BEFORE UPDATE ON product
FOR EACH ROW
BEGIN
IF NEW.product_id = 54 THEN
UPDATE orders SET Stat = 'done' WHERE order_id = NEW.order_id
END IF;
END;
Upvotes: 0
Reputation: 366
do this it work for u
mysql_query("UPDATE orders SET stat='done' where product_id in (select product_id from product WHERE product_id='54'");
Upvotes: 0
Reputation: 44874
You need to use JOIN something as
update orders
join product on product.order_id = orders.order_id
set
orders.Stat='done'
where product.product_id = '54'
Upvotes: 5