Update one table based on another table

[deansatch]

I have 2 tables, one for products (which includes ALL products and stock quantities) and one for picking lists (basically a shopping cart)

 

When the query is submitted, I want it to get the quantities from 'pickingLists' table for each of the products in that query, and update the quantities of those products in the 'products' table. At the moment, what I have does exactly that, but it also sets the qty of every other product as 0. I just want it to perform the update on the relevant rows, not all of them.

 

mysql_query("UPDATE products t1 SET  qty = qty-(SELECT qty FROM pickingLists t2 WHERE t2.productName = t1.productName AND t1.size= t2.size AND  t2.listID = '$listID')") or die(mysql_error());

 

Where am I going wrong?

[Muddy_Funster]

You need to have another WHERE outside your SELECT to tell the update which record it is to update, otherwise it will, as you have found out, update every record.

ysql_query("UPDATE products t1 SET  qty = qty-(SELECT qty FROM pickingLists t2 WHERE t2.productName = t1.productName AND t1.size= t2.size AND  t2.listID = '$listID')   WHERE table.value = uniqueIdentifier   ") or die(mysql_error());

[deansatch]

That's what I thought, but when I add the similar WHERE outside the select, it doesn't seem to recognise the pickingList table:

 

mysql_query(
"UPDATE products t1 SET  qty = qty-(SELECT qty FROM pickingLists t2 
WHERE t2.productName = t1.productName AND t1.size= t2.size AND t1.colour = t2.colour AND t2.listID = '$listID') 
WHERE t2.productName = t1.productName AND t1.size= t2.size AND t1.colour = t2.colour") or die(mysql_error());

 

I get error: Unknown column 't2.productName' in 'where clause'

[Muddy_Funster]

try not aliasing your table names.

[deansatch]

Thanks...I have just got it working though.

 

mysql_query("UPDATE products t1 ,pickingLists t2
SET  t1.qty = t1.qty-(SELECT t2.qty FROM pickingLists t2 WHERE t2.productName = t1.productName AND t1.size= t2.size AND t1.colour = t2.colour AND t2.listID = '$listID') 
WHERE t2.productName = t1.productName AND t1.size= t2.size AND t1.colour = t2.colour") or die(mysql_error());

 

This seems to work...hopefully it's not flawed!