[SOLVED] Delete table1.row inner join != table2.row

[quasiman]

I have two tables with similar data, their sku numbers being the primary similarity.  In one table, I have extra rows that I don't need, and I'd like to delete them.  So below I have my script...but instead of deleting only the rows that don't match, it deletes all records.

Also, the script keeps running continuously so I think it's in an endless loop somehow.

 

Any help would be greatly appreciated!

<?php
ini_set('display_errors', 1);
ini_set('log_errors', 1);
ini_set('error_log', dirname(__FILE__) . '/error_log.txt');
error_reporting(E_ALL);
include ('dbconnect.php');
$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($dbase, $linkID) or die("Could not find database.");

$sql = "DELETE bad_rows.*
       FROM zdata_prodfile
        AS good_rows
       INNER JOIN jos_vm_product
        AS bad_rows
          ON (bad_rows.product_sku != good_rows.SKU)";

$query = mysql_query($sql, $linkID) or die(mysql_error());
printf("Records deleted: %d\n", mysql_affected_rows());
mysql_close($linkID) or die(mysql_error());
?>

[ignace]

Run this first to see which will be deleted:

SELECT * FROM zdata_prodfile gr WHERE gr.sku NOT IN (SELECT * FROM jos_vm_product br WHERE br.product_sku);

 

Then execute:

DELETE FROM zdata_prodfile gr WHERE gr.sku NOT IN (SELECT * FROM jos_vm_product br WHERE br.product_sku);

[quasiman]

Thank you for the reply,

I'm not sure I follow your subquery...

(SELECT * FROM jos_vm_product br WHERE br.product_sku);

 

Shouldn't the WHERE clause have an equal to value?

[quasiman]

Well I tried it anyway, and I'm getting this error:

Operand should contain 1 column(s)

[quasiman]

Never mind, I rewrote a little and it works 

$sql = "SELECT br.product_sku 
		FROM jos_vm_product br 
		WHERE br.product_sku 
		NOT IN 
			(SELECT gr.SKU FROM zdata_prodfile gr)";

I think I can figure out the rest from here.

 

Thanks!

[ignace]

Never mind, I rewrote a little and it works 

$sql = "SELECT br.product_sku 
		FROM jos_vm_product br 
		WHERE br.product_sku 
		NOT IN 
			(SELECT gr.SKU FROM zdata_prodfile gr)";

I think I can figure out the rest from here.

 

Thanks!

 

Oeps my bad forgot that the subquery could only return one value, sorry