MySQL problem: You can ' t specify target table ' table name ' for the update in FROM clause

In MySQL, when writing SQL statements, you may encounter the error of you can ' t specify target table ' table name ' for update in FROM clause, which means that you cannot select some values from the same table before the UPD Ate this table (in the same statement).

1. How did the problem arise?

Data preparation

Create table t_person (Pid int primary key auto_increment,pname varchar (20)) ; Insert into t_person (PName)  values (' Chen Yi '); Insert into t_person (pName)  values (' Huang ER Insert into t_person (pName)  values (' Zhang San '); Insert into t_person (pName)  values (' Zhang San '); Insert into t_person (pName)  values (' John Doe '); Insert into t_person (PName)  values (' John Doe '); Insert into t_person (pName)  values (' Harry '); Insert into t_person (pName)   VALUES (' Zhao Liu '); Insert into t_person (pName)  values (' Zhao Liu '); Insert into t_person (PName)  values (' money Seven '); Insert into t_person (pName)  values (' Harry '); Insert into t_person ( PName)  values (' John Doe '); Insert into t_person (pName)  values (' Sun Eight '); insert into t_ Person (pName)  values (' Yang Jiu '); Insert into t_person (pName)  values (' Zhang San ');insert into  T_person (PName)  values(' Wu 10 '); select * from t_person;

The data are as follows:

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/83/F4/wKioL1eBVDOTxUm3AAAZFsQZuUg131.png "title=" Init_ Data. PNG "alt=" Wkiol1ebvdotxum3aaazfsqzuug131.png "/>

By looking at the data in the table, we can know that it contains duplicate data: "Zhang San", "John Doe" and so on.

What exactly are the names that repeat?

SELECT pName from T_person GROUP by PName have COUNT (pName) >1;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/83/F4/wKioL1eBVPqRJsQ5AAAHCcfiHu4669.png "title=" Duplicate_data. PNG "alt=" Wkiol1ebvpqrjsq5aaahccfihu4669.png "/>

Next, we delete all records that have duplicate names.

DELETE from T_personwhere pName in (SELECT pName to T_person GROUP by PName have COUNT (pName) >1);

At this point, we will encounter an error:

Error code: 1093You can ' t specify target table ' T_person ' for update in FROM clause

2, how to solve the problem

Avoid the error by passing the result of the select again through the intermediate table select again.

For example:

DELETE from T_personwhere pName in (select PName from (select PName from T_person GROUP by PName have COUNT ( PName) (>1) as temp);

Re-use View data

SELECT * from T_person;

The results are as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/83/F5/wKiom1eBVnCRqcinAAALtkbkmRk873.png "title=" result _data. PNG "alt=" Wkiom1ebvncrqcinaaaltkbkmrk873.png "/>

Note that this problem only occurs with MySQL, MSSQL and Oracle do not appear this problem.

MySQL problem: You can ' t specify target table ' table name ' for the update in FROM clause