Re: Updating a table when using LEFT JOIN

From the Mysql docs: Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables: UPDATE items,month SET items.price=month.price WHERE items.id=month.id; The example shows an inner join using the comma operator, but multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN. Note: You cannot use ORDER BY or LIMIT with multiple-table UPDATE. -Micah On Thursday 26 August 2004 03:23 pm, Chris Payne wrote: > Hi there everyone, > > > > I am using the following to grab the data I need from several tables: > > > > $sql = "SELECT * FROM vendorprices LEFT JOIN fooditems on > (vendorprices.FoodItemNumber = fooditems.FoodItemID) WHERE > vendorprices.VendorNumber='$VendorID' ORDER BY > vendorprices.VendorItemNumber"; > > > > This works great, very fast etc .. the problem is, I then need to give the > option for them to edit the items - again, not a problem in populating the > form - until I have to then use the UPDATE function, how can I update each > item in separate tables when I use the above join to grab the info in the > first place? I've never had to write to 2 tables at once where data is > relative like this, infact it was my first time of using JOINS at all to > even display the data. > > > > I'm using PHP with MySQL. > > > > Thank you. > > > > Chris