Unlike Oracle and Sqlserver,mysql, which do not support querying the data of a table and querying for the updated condition, we need to use the following statement bypass:
UPDATETeaching_departmentSETCode_year= ., Notice_code=(SELECTA.code+ 1 from(SELECT MAX(Notice_code) Code fromTeaching_departmentWHEREdepartment_id= 6284 andCode_year= .AWHEREId= 106;
Local tests are passed, but when you go up to the test environment, you will still see the following error:
[SQL] UPDATETeaching_departmentSETCode_year= ., Notice_code=(SELECTA.code+ 1 from(SELECT MAX(Notice_code) Code fromTeaching_departmentWHEREdepartment_id= 6284 andCode_year= .AWHEREId= 106;[ERR] 1093 -You can't specify target table'Teaching_department'For update in from clause
Compared to the version found that the local is 5.6.25, the test environment is 5.7.10, the version is not supported by the high. This can only be done in a different way:
UPDATETeaching_department, (SELECT MAX(Notice_code) Code fromTeaching_departmentWHEREdepartment_id= 6284 andCode_year= .) asTSETCode_year= ., Notice_code=T.code+ 1 WHEREId= 7
This notation is supported in all editions. syntax See update Syntax
It was later found that this was a bug in MySQL 5.7.6 and was fixed in version 5.7.11. Reference
About MySQL 5.7 version "reported [ERR] 1093-you can ' t specify target table ' XXX ' for update on FROM clause" bug