What is wrong with the SQL DELETE FROM syntax?

Question

I am trying to delete 96k entries.

delete all entries in the xoops_bb_posts_text tables that do not have the corresponding post_id for xoops_bb_posts

This query worked with 91k records return:

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 these entries, I received a syntax error, but I can’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 mistake?

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 the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax for use next to 'WHERE does not exist (select post_id from xoops_bb_posts p WHERE p.post_id = t.post_' on line 2

Answer 1

For me, this problem is easier to solve with the delete using outer join and looking for strings that didn't match. Something like that:

 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 just by changing your request:

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

Answer 2

If you use aliases in the delete call, you should use an alias as an argument:

DELETE alias FROM tablerealname as ALIAS ...

So, in the original OP question, he just has to add an 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 ) 

Answer 3

You cannot rename table names in single-table DELETE in MySql . You need to use the full name of the table, for example:

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

Answer 4

You cannot specify a table alias in the delete statement.

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

Answer 6

 CREATE TABLE IF NOT EXISTS `tbl_student1` ( `stud_id` int(11) NOT NULL AUTO_INCREMENT, `stud_name` varchar(255) CHARACTER SET utf8 NOT NULL, `stream_id` int(11) NOT NULL, `address` varchar(255) CHARACTER SET utf8 NOT NULL, `s_date` datetime NOT NULL, `status` int(11) NOT NULL, PRIMARY KEY (`stud_id`), FOREIGN KEY ('stream_id') REFERENCES tbl_stream ('stream_id') ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ; 

Answer 7

I do not know the syntax "EXISTS". I think it's better to use "in" and "not in"

Try:

 DELETE FROM xoops_bb_posts_text WHERE post_id not in (SELECT post_id FROM xoops_bb_posts)