What is wrong with this SQL DELETE FROM syntax?

Question

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 returning 91k records:

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

when I tried to delete those records I got a syntax error, but I don't see it.

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

Where is the error?

Error

SQL query: Documentation

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

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE not exists (select post_id from xoops_bb_posts p WHERE p.post_id = t.post_' at line 2

Answer 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  
)