[RESOLVED] You can't specify target table for update in FROM clause

DKY

Here's my issue. I'm trying to remove records from a table that do not exist in another table so I'm comparing the tables and doing something like this

DELETE FROM tblmaxxinv WHERE NAV_PN IN (SELECT tblvisiblepsdbsumoneweekpfil.NAV_PN FROM tblvisiblepsdbsumoneweekpfil LEFT JOIN tblmaxxinv ON tblvisiblepsdbsumoneweekpfil.NAV_PN = tblmaxxinv.NAV_PN WHERE (((tblmaxxinv.NAV_PN) Is Null)))

only to get this

You can't specify target table 'tblmaxxinv' for update in FROM clause

So obviously it's saying that I can't refer to itself to delete from itself so is there another way around this?

bradgrafelman

Do a join in the DELETE statement itself rather than using a subquery, e.g.:

DELETE tblmaxxinv
FROM tblvisiblepsdbsumoneweekpfil pf
    LEFT JOIN tblmaxxinv inv ON pf.NAV_PN = inv.NAV_PN
WHERE inv.NAV_PN IS NULL;

EDIT: Note that the above query is untested, so make sure you test it out on a development server in case it deletes more than you had expected.

DKY

Hmmm

Unknown table 'tblmaxxinv' in MULTI DELETE

I know the table is there and exists...

bradgrafelman

Woops, that'd be my bad... can't seem to read properly:

MySQL Manual wrote:
If you declare an alias for a table, you must use the alias when referring to the table

Thus, try changing "DELETE tblmaxxinv" to "DELETE inv" instead. :o

DKY

Hmm, this doesn't seem to be doing what I'm looking to do. Maybe my intial code is wrong. I'm trying to look at the tblmaxxinv table and if there are any NAV_PN on that table that are not on the tblvisiblepsdbsumoneweekpfil table then delete them from the tblmaxxinv. I added a record to the tblmaxxinv that is not on the tblvisiblepsdbsumoneweedpfil table and it still is there after I run the program.

$remove_query = "DELETE inv FROM tblvisiblepsdbsumoneweekpfil pf LEFT JOIN tblmaxxinv inv ON pf.NAV_PN = inv.NAV_PN WHERE inv.NAV_PN IS NULL";
if (!($remove_result = mysql_query($remove_query)))
{
	$mcrmessage = mysql_error();
	echo $mcrmessage . "<br><br>" . $remove_query;
	die();
}

bradgrafelman

Oh, I see. In that case, try changing "LEFT JOIN" to a "RIGHT JOIN" instead.

DKY

Nope, that didn't work either...

bradgrafelman

Sorry, just re-read the MySQL manual after I doubted myself on my last reply.

Try reversing the order of the tables in the JOIN.

EDIT: Also note that another way of going about doing this might be:

DELETE FROM tblmaxxinv
WHERE NAV_PN NOT IN (
    SELECT NAV_PN
    FROM tblvisiblepsdbsumoneweekpfil
)

though I'm not sure which one is more optimized/efficient (and thus faster).

DKY

This is an awesome little bit of code here, thank you!

$remove_query = "DELETE FROM tblmaxxinv WHERE NAV_PN NOT IN ( SELECT NAV_PN FROM tblvisiblepsdbsumoneweekpfil )";