update data by using subquery?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
valen53
Forum Contributor
Posts: 137
Joined: Tue Aug 27, 2002 9:29 am

update data by using subquery?

Post by valen53 »

i have 2 table which is,

Order_detail and Item_detail, its use item_id to link each other.

Now, i want to update data from Item_detail to Order_detail.

Code: Select all

sql = "update Order_detail set Pqty = ( select pqty from Item_detail ) where Pqty = 0 ; "
But the problem is Sql dunno update which record. Because didn't join 2 table. But i dunno how to make this 2 table to link it in this subquery.

Anyone can help me ?
Thank u
valen53
Forum Contributor
Posts: 137
Joined: Tue Aug 27, 2002 9:29 am

Post by valen53 »

i settle the problem.

Code: Select all

UPDATE SO_detail INNER JOIN item_detail ON SO_detail.item_id = item_detail.item_id SET SO_detail.Fqty = Item_detail.Pqty
WHERE ((їSO_detail].їFqty])=0);
above 1 is ms. access format
Post Reply