MySQL common UPDATE operations and mysqlupdate operations

MySQL common UPDATE operations and mysqlupdate operations

Overview

Test environment: mysql 5.6.21

 

Procedure

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' product 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 = 'commodity table'; create table 'producttype' ('id' int (11) not null comment 'item category (0 fresh food, 1 food, 2 life) ', 'amount' int (11) comment' total item amount for 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 a single table

UPDATE productSET price='20.00',type=0 WHERE proID=2;

Associated update operations

UPDATE producttype,productSET producttype.amount=product.pricewhere product.TYPE = producttype.ID AND product.TYPE=1;

1. query all 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 four matching records and three actually updated records. By default, the join 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. Three Rows that meet the condition are queried, and no unrelated Rows are queried.

Summary

The Association operation of mysql update is not followed by the FROM statement in mysql 5.6. It is only possible to UPDATE all connected tables and then the WHERE statement. If there is something else about database products, pay attention to it here.

 

If the article is helpful to you, please recommend it. Thank you !!!

 

Note: Author: pursuer. chen Blog: http://www.cnblogs.com/chenmh All essays on this site are original. You are welcome to repost them. However, you must indicate the source of the article and clearly provide the link at the beginning of the article. Otherwise, you will be held accountable. Welcome to discussion

 

--- Restore content end ---