Update one table from two others

[quasiman]

I'm trying to zero out products in my shop that don't have a match in two other tables.  When I run this query, it kills my MySql process load, and eventually crashes the server (500 error).

I'd appreciate any advice to run this better!

<?php
$query = "UPDATE shop_products p,inventory1 1,inventory2  2 
			SET 
					p.product_in_stock = '0' 
			WHERE 
					p.product_sku != 1.SKU 
			AND 
					p.product_sku != 2.PartNumber";
$result = mysql_query($query);

if (!$result) {
	throw new Exception('You fail: ' . mysql_error($db));
} else {
	echo "Inventory Updated";
}
?>

 

Just as a side note, I tried this as a SELECT statement in PHPMyAdmin, with very similar results.

SELECT product_sku,SKU,PartNumber 
FROM shop_products,inventory1,inventory2 
WHERE 
product_sku != SKU 
AND 
product_sku != PartNumber

[ignace]

UPDATE shop_products p,inventory1 i1,inventory2  i2

            SET

                  p.product_in_stock = '0'

            WHERE

                  p.product_sku != i1.SKU

            AND

                  p.product_sku != i2.PartNumber

[quasiman]

I may be dense, but I don't see how yours is different than mine....you changed the aliases?

[ignace]

I may be dense, but I don't see how yours is different than mine....you changed the aliases?

 

Yes. I think you can't use a number as an alias.