Remove duplicate data from mysql

Select can take alias, delete cannot.

1. When you use mysql to perform the delete from operation, if the FROM Statement of the subquery and the update/delete object use the same table, an error will occur.

Mysql> delete from tab1 WHERE col1 = (select max (col1) FROM tab1 );
ERROR 1093 (HY000): You can't specify target table 'tab1' for update in FROM clause

In most cases, you can add a select alias table to solve the "same table" restriction.

Delete from tab1
WHERE col1 = (
Select max (col1)
FROM (
SELECT * FROM tab1
) AS t
);

 

------------------------------------------------------------------------

2. When mysql delete from where in is followed by a query statement, the where condition cannot be added.

 

SQL code
Delete from 't_ goods 'where fi_id in (select * from (select fi_id from 't_ goods' where fs_num is null and fs_name is null and fs_type is null andfs_using is null and fs_lifetime is null) b)

 

SQL code
Delete from 't_ goods 'where fi_id in (select fi_id from 't_ goods' where fs_num is null and fs_name is null and fs_type is null andfs_using is null and fs_lifetime is null)

 

SQL code
Delete from 't_ goods 'where fi_id in (select fi_id from 't_ goods ')

In the preceding three cases, only the intermediate one cannot be executed.

In general, the where condition cannot be added to the query statement after mysql delete from where in.

---------------------------------------------------------------------------

 

3. delete from table... the table cannot use an alias.

 SQL code

Delete from student a where a. id in (1, 2); (execution failed)

Select a. * from student a where a. id in (1, 2); (executed successfully)

Delete from v9_news WHERE title IN (SELECT title FROM v9_news as newsb WHERE FROM_UNIXTIME (newsb. inputtime, '% Y-% m-% D') = '2017-06-27' group by newsb. title having count (*)> 1) B)
AND id not in (SELECT id FROM (select min (id) AS id FROM v9_news WHERE FROM_UNIXTIME (inputtime, '% Y-% m-% D ') = '1970-06-27 'group by title having count (*)> 1) c); TRUE !!
Delete from v9_news AS news
WHERE news. title IN (SELECT title FROM v9_news as newsb WHERE FROM_UNIXTIME (newsb. inputtime, '% Y-% m-% D') = '2017-06-27' group by newsb. title having count (*)> 1)
AND id not in (select min (id) FROM v9_news WHERE FROM_UNIXTIME (inputtime, '% Y-% m-% D ') = '1970-06-27 'group by title having count (*)> 1); FALSE !!