Summary of MySQL Delete Problems

Internal issues related to the MySQL database

Cause Delete from table where Col not in (select Col from Table group by XX)

An error is reported.

What we need to do is

CopyCodeThe Code is as follows: Create Table TMP selete Col from Table group by xx;
Delete from table where Col not in (select Col from TMP );
Drop table TMP;

Today, when developing your own project, you need to import the original data of the project to the new system. In this process, some dirty data is generated.

We need to delete this dirty data. During the deletion process, the delete SQL statement that I wrote cannot be executed. I don't know why, so I can execute the same statement.

Select is fine.

For example:
Delete from student a where a. ID in (1, 2 );()
Select a. * from student a where a. ID in (1, 2 );()
Why?
Result processing:

Delete: The operation is successful without alias when writing a table!

For example:

Delete from student where ID in (1, 2 );()
MySQL version: 5.1.30-Community-Log

This is a small problem encountered during development.