What Is Wrong With This SQL DELETE FROM Syntax?

I'm trying to delete 96k records. delete all the records in table xoops_bb_posts_text pages that don't have a have a matching post_id to xoops_bb_posts This query worked returnin

Solution 1:

If you alias tables in a delete call, you have to use the alias as the argument:

DELETE alias FROM tablerealname as ALIAS ...

So in OP's original question, he simply has to add the alias after DELETE:

DELETE t FROM xoops_bb_posts_text as t WHERE NOT EXISTS (  
SELECT post_id  
FROM xoops_bb_posts as p  
WHERE p.post_id = t..post_id  
)  

Solution 2:

To me, this problem is more easily solved by using a delete statement with a an outer join and looking for the rows that had no match. Something like this:

delete t from xoops_bb_posts_text as t
left outer join xoops_bb_posts as p
on p.post_id = t.post_id
where p.post_id is null;

or simply changing your query:

DELETE t
FROM xoops_bb_posts_text t  
WHERE not exists 
      (select post_id from xoops_bb_posts p WHERE p.post_id = t.post_id );

Solution 3:

You cannot alias table names in single-table DELETE in MySql. You need to use full table name, like this:

DELETE FROM xoops_bb_posts_text
WHERE not exists (select post_id from xoops_bb_posts p WHERE p.post_id = xoops_bb_posts_text.post_id );

Solution 4:

You can not specify a table alias in the delete statement.

Solution 5:

From my comment:

DELETE FROM xoops_bb_posts_text t  
 WHERE NOT EXISTS (
    SELECT * 
      FROM xoops_bb_posts p 
     WHERE p.post_id = t.post_id 
);