[SOLVED] join tables error

[quasiman]

I've got something backwards here, and I can't see what it is.

I have two tables: products and inventory.  Products contains most of the information about the product, inventory contains the same SKU ID, the actual number of products onhand, and prices.
I'm trying to update the products table with the inventory tables on hand amount.

[code]<?php

mysql_connect("localhost", "LOGIN", "PASSWORD");

mysql_select_db("DATABASE");


$results = mysql_query("UPDATE PRODUCTS SET onhand = INVENTORY.onhand FROM INVENTORY JOIN INVENTORY ON INVENTORY.sku = PRODUCTS.sku")
or die(mysql_error());
?>[/code]

Any help would be greatly appreciated!

[btherl]

Try this:

[code=php:0]UPDATE PRODUCTS SET onhand = INVENTORY.onhand FROM INVENTORY WHERE INVENTORY.sku = PRODUCTS.sku[/code]


You don't need to join in this case.  What you're doing is more like "SELECT INVENTORY.onhand FROM INVENTORY WHERE INVENTORY.sku = PRODUCTS.sku" for each row of PRODUCTS.

[quasiman]

hmm...I'm getting basically the same error:
[quote]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM INVENTORY WHERE PRODUCTS.sku = INVENTORY.sku' at line 1[/quote]

I'm really not seeing anything wrong with this:
[code]<?php

mysql_connect("localhost", "LOGIN", "PASSWORD");

mysql_select_db("DATABASE");


mysql_query("UPDATE PRODUCTS SET onhand = INVENTORY.onhand FROM INVENTORY WHERE PRODUCTS.sku = INVENTORY.sku")
or die(mysql_error());
?>[/code]

[btherl]

Oops, you can't "update from" in mysql.  It doesn't support the syntax.

Instead you should be able to do

[code=php:0]UPDATE PRODUCTS SET onhand = (SELECT onhand FROM INVENTORY WHERE INVENTORY.sku = PRODUCTS.sku)[/code]

[quasiman]

Perfect, Thank You!!