Page 1 of 1

update data by using subquery?

Posted: Tue Feb 17, 2004 10:20 pm
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

Posted: Wed Feb 18, 2004 12:24 am
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