CODEWITHSUNDEEP
phpmysqlsqlsql-update
PersianHero
PersianHero

Reputation: 123

update sql table based on value in another table in PHP

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

Answers (3)

sternze
sternze

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

amit 1984
amit 1984

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

Abhik Chakraborty
Abhik Chakraborty

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

Related Questions