update sql table based on value in another table in PHP

Question

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 :)

Please, please please stop using the mysql_* extension. It's deprecated, issues notices, is unsafe, not maintained and will be removed some time in the future. Use mysqli_* or PDO instead — Elias Van Ootegem
– Elias Van Ootegem, Commented Jun 18, 2014 at 12:41
Thanks for advising. Do you mean I should use it like this? mysqli_query — PersianHero
– PersianHero, Commented Jun 18, 2014 at 13:48
Yes, but you should learn the alternative extensions because, even though at first glance mysqli_* looks a lot like mysql_*, they don't translate 1 on 1: the new extensions are a bit more complicated, and a lot more powerful. — Elias Van Ootegem
– Elias Van Ootegem, Commented Jun 18, 2014 at 13:49

Abhik Chakraborty · Accepted Answer · 2014-06-18 12:39:26Z

5

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'

answered Jun 18, 2014 at 12:39

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bogdan Over a year ago

and also use order.Stat as it exists in the order table - so with a capital letter.

amit 1984 · Accepted Answer · 2014-06-18 12:41:59Z

0

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'");

answered Jun 18, 2014 at 12:41

amit 1984

3663 silver badges9 bronze badges

Comments

user229044 · Accepted Answer · 2017-06-17 12:12:01Z

0

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;

edited Jun 17, 2017 at 12:12

user229044♦

241k41 gold badges348 silver badges350 bronze badges

answered Jun 18, 2014 at 12:42

sternze

1,6442 gold badges10 silver badges15 bronze badges

Collectives™ on Stack Overflow

update sql table based on value in another table in PHP

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related