MySQL common UPDATE operations

MySQL common UPDATE operations
Overview test environment: mysql 5.6.21 step CREATE test table create table 'product' ('proid' int (11) not null AUTO_INCREMENT COMMENT 'item TABLE Primary key', 'price' decimal) not null comment 'item price', 'type' int (11) not null comment' item category (0 fresh, 1 food, 2 life )', 'dtime' datetime not null comment' creation time', primary key ('proid') ENGINE = InnoDB AUTO_INCREMENT = 1 default charset = utf8 COMMENT = 'item table '; create table 'producttype' ('id' int (11) NOT NULL COMME NT 'item category (0 fresh food, 1 food, 2 life) ', 'amount' int (11) comment' total amount of each category ', unique key ('id ')) ENGINE = InnoDB default charset = utf8 COMMENT = 'item category funding summary' INSERT test data insert into product (price, type, dtime) VALUES (10.00, 0, now ()), (10.00, 1, now (), (10.00, 1, now (), (20.00, 2, now (), (30.00, 3, now ()); insert into producttype (ID) VALUES (1), (2), (3); SELECT * FROM product; SELECT * FROM producttype; UPDATE productSET price = '20 for a single table UPDATE operation. 00', type = 0 WHERE proID = 2; UPDATE producttype and productSET producttype for associated UPDATE operations. amount = product. pricewhere product. TYPE = producttype. id and product. TYPE = 1; 1. query all the fields insert into producttype (ID) VALUES (4); UPDATE producttypeSET producttype. amount = (SELECT ifnull (SUM (product. price), 0.00) FROM product WHERE product. TYPE = producttype. id group by product. TYPE); Note: As you can see, rows matched: 4 indicates that the number of qualified records is 4. Three actually updated records are associated. By default, the statement does not determine the actual associated rows. 2. only query the fields to be updated: UPDATE producttypeSET amount = null; UPDATE producttype, product SET producttype. amount = (SELECT ifnull (SUM (product. price), 0.00) FROM product WHERE product. TYPE = producttype. id group by product. TYPE) where product. TYPE = producttype. ID; use the following method: Rows matched: 3. The row that meets the condition is three Rows, and no unrelated Rows are queried. Summary The Association operation of mysql update cannot be directly followed by the FROM statement in mysql 5.6. It can only be used to UPDATE all connected tables and then the WHERE statement. If there are other database products, pay attention to it here.