update problem

[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.

 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

[LordShryku]

Multi-table updates are available sice MySQL 4.0.4.

UPDATE Order_detail,Items_detail 
   SET Order_detail.Pqty = Item_detail.pqty
 WHERE Order_detail.item_id = Item_detail.item_id
   AND Order_detail.Pqty = 0 

As a side note, you really should come up with a standard naming scheme for yout tables/fields. Mixing of uppercase and lowercase is generally a no-no.

[valen53]

thank you very much