MySQL UPDATE statement Tips

I. Multi-table Update

1. Data preparation

Mysql>Mysql> SelectGOODS_ID, Goods_name,goods_cate fromTdb_goods;+----------+---------------------------------------+---------------------+|goods_id|Goods_name|Goods_cate|+----------+---------------------------------------+---------------------+|        1 |R510vc the.6-inch Notebook|Notebook||        3 |g150th the.6-Inch game Ben|Game Ben||        4 |x550cc the.6-inch Notebook|Notebook||        7 |Svp13226scb -.3-inch touch-controlled ultra-high-|Super Ben||        9 |IPad Air md788ch/A9.7-Inch WiFi version|Tablet||       Ten |IPad Mini Me279ch/A7.9 inch|Tablet||        - |AT7-7414LP Desktop PC|Desktop||        the |Z220SFF F4F06PA Workstation|Server/Workstation||        - |PowerEdge T110 II Server|Server/Workstation|+----------+---------------------------------------+---------------------+MySQL>Mysql> Select *db_goods_cates;+---------+---------------------+|cate_id|Cate_name|+---------+---------------------+|       1 |Desktop||       2 |Tablet||       3 |Server/Workstation||       4 |Game Ben||       5 |Notebook||       6 |Super Ben|+---------+---------------------+

View Code

2. Update the Goods_cate field in the Tdb_goods table to the cate_id field of the Tdb_goods_cates table

UPDATE tdb_goods g INNER JOIN tdb_goods_cates c on g.goods_cate = c.cate_name SET g.goods_cate = c.cate_id;

Two. Update the table containing itself in the filter condition

1. Data preparation

-----------------------------------------------------------------------------MySQL> Select *  fromstudent;+----+--------+----------+---------+|Id|Name|Idcardno|Iscadre|+----+--------+----------+---------+|  1 |Tom| 350020   |    NULL ||  2 |Jim| 350022   |    NULL ||  3 |Lucy| 460311   |    NULL ||  4 |Liming| 733098   |    NULL |+----+--------+----------+---------+MySQL> Select *  fromcadre;+----+--------+----------+|Id|Name|Idcardno|+----+--------+----------+|  1 |Tom| 350020   ||  2 | Max    | 636095   ||  3 |Liming| 733098   |+----+--------+----------+

View Code

2. Suppose you need to update the iscadre of the student table with the same record as the Cadre table Idcardno field to 1

Usually we write this:

UPDATE student SET iscadre=1 WHERE Student.idcardno in

(SELECT S.idcardno from student S INNER joins cadre C on S.idcardno=c.idcardno);

But in MySQL error, "error 1093 (HY000): You can ' t specify target table ' student ' for the update in FROM clause", MySQL does not support this filter condition contains its own Update (Oracle CAN).

The correct approach is as follows:

UPDATE Student A Join (SELECT S.idcardno from student s INNER JOIN cadre C on S.idcardno=c.idcardno) b

On a.idcardno = B.idcardno SET a.iscadre = 1;

　　

MySQL UPDATE statement Tips