仅允许1行时删除最后插入的行SQL

Question

I was wondering why my delete was not functioning.

I am trying to DELETE SQL row, the last SQL row inserted when only 1 row with an ip address and a user id is accepted and only one row with an ip address and a blank user id is accepted.

Here is my code:

DELETE
FROM visitor
WHERE ip_address NOT IN
    (SELECT ip_address
     FROM visitor
     WHERE ip_address = '".$ipAddress."'
         AND user_id = ''
     ORDER BY user_id DESC LIMIT 1)

Why is my SQL row not DELETING?

Answer 1

Simple: because it's not allowed to use the table from which to delete in the sub-query. Just run the SELECT query separatly, and use the result to perform the DELETE...

RTM, it clearly states this is not allowed here:

Incorrectly used table in subquery:

Error 1093 (ER_UPDATE_TABLE_USED)
SQLSTATE = HY000
Message = "You can't specify target table 'x'
for update in FROM clause"
This error occurs in cases such as the following, which attempts to modify a table and select from the same table in the subquery:

UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);

You can use a subquery for assignment within an UPDATE statement because subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table (in this case, table t1) for both the subquery FROM clause and the update target.

Particularly the last sentence is important: However, you cannot use the same table (in this case, table t1) for both the subquery FROM clause and the update target.

There is a hacky way around this, it's silly but it worked last time I checked, which is to wrap your sub-query in another sub (WHERE x IN (SELECT id FROM (SELECT id FROM tbl...))). But that's just awful in every way