Mysql:you can ' t specify target table problem resolution

First create a table:

CREATE TABLE  INT(all nullDEFAULTnullVARCHAR(20  nullDEFAULTnull)

Insert several data:

Mysql> Select *  fromT1;+------+------+|Id|Name|+------+------+|    1 |Chen||    2 |Li||    3 |Huan|+------+------+3Rowsinch Set(0.00Sec

Requirement 1: Delete the maximum ID of the record, so we will write about the following statement:

MySQL>Deletefromwhere id=(selectmaxfrom   1093 (HY000): You can "T1 ' for the update in from   Clause
Unfortunately, it's an error.

Can be modified to the following statement:

Mysql> DeleteA fromT1 A, (Select Max(ID) Maxid fromT1) bwherea.ID=B.maxid; Query OK,1Row affected (0.01sec) MySQL> Select *  fromT1;+------+------+|Id|Name|+------+------+|    1 |Chen||    2 |Li|+------+------+2Rowsinch Set(0.00Sec

It can also be the following statement:

Mysql> Delete  fromT1whereIdinch(SelectA.maxid from(Select Max(ID) Maxid fromt1) a); Query OK,1Row affected (0.01sec) MySQL> Select *  fromT1;+------+------+|Id|Name|+------+------+|    1 |Chen|+------+------+1Rowinch Set(0.00Sec

Requirement 2: Insert a record, and the ID value is the maximum value of the table plus 1, so we will write about the following statement:

MySQL>insertintovalues((selectmax from T1), '  You '  1093 (HY000): You can"T1' for the update in from Clause
Still reported the same mistake.

Can be rewritten as follows:

Mysql> Insert  intoT1Select(Select Max(ID)+1Maxid fromT1),' You'; Query OK,1Row affected (0.06sec) Records:1Duplicates:0Warnings:0MySQL> Select *  fromT1;+------+------+|Id|Name|+------+------+|    1 |Chen||    2 |You|+------+------+2Rowsinch Set(0.00Sec

Requirement 3: We want to update a statement, the ID needs to change to the previous maximum value plus 1, so we will write about the following statement:

MySQL>updateset id=(selectmax(ID)+1  from where id=11093 (HY000): You can' T1' for update on from clause
Wrong as ever

We can rewrite the following statement:

Mysql> UpdateT1, (Select Max(ID)+1  asMaxid fromT1) ASetId=A.maxidwhereId=1; Query OK,1Row affected (0.00sec) Rows matched:1Changed:1Warnings:0MySQL> Select *  fromT1;+------+------+|Id|Name|+------+------+|    3 |Chen||    2 |You|+------+------+2Rowsinch Set(0.00Sec

You can also change to the following statement:

MySQL> UpdateT1SetId=(SelectA.maxid from(Select Max(ID)+1Maxid fromT1) a)whereId=3; Query OK,1Row affected (0.01sec) Rows matched:1Changed:1Warnings:0MySQL> Select *  fromT1;+------+------+|Id|Name|+------+------+|    4 |Chen||    2 |You|+------+------+2Rowsinch Set(0.00Sec

The general idea is to convert the query's maximum statement to subquery or derived.

Mysql:you can ' t specify target table problem resolution