mysql中怎么修改多个字段的数据

1、创建测试表，

create table test_update_cols(id int,value varchar(20))

2、插入测试数据；

insert into test_update_cols values (1,'v1')

insert into test_update_cols values (2,'v2')

insert into test_update_cols values (3,'v3')

insert into test_update_cols values (4,'v4')

3、查询表中全量数据；select t.* from test_update_cols t

4、编写语句，同时更新id和value两个字段；

 update test_update_cols set id = id+100, value = concat(value,'00')

5、编写语句，重新查询数据，可以发现两个字段已经被更新；select t.* from test_update_cols t

嘿嘿，本来也以为UPDATE 语句只可以对单表 *** 作，

后来还是查了一下，发现MYSQL 可以对多表更新：

13.2.10. UPDATE Syntax

单表更新的语法：

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name

SET col_name1=expr1 [, col_name2=expr2 ...]

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

多表更新的语法：

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references

SET col_name1=expr1 [, col_name2=expr2 ...]

[WHERE where_condition]

单表就不说了，下面讲讲多表：

For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.

要英文不好看就看看例子吧：

UPDATE items,month SET items.price=month.price

WHERE items.id=month.id

讲讲table_references:

其实就是一个连接方式：join

你要多表做更新 *** 作，肯定要把表关联起来。

你的例子中：

mysql>insert into abc(a) values('1')

Query OK, 1 row affected, 5 warnings (0.02 sec)

mysql>insert into def(a) values('1')

Query OK, 1 row affected, 5 warnings (0.00 sec)

mysql>select * from abc,def

+------+---+---+---+---+---+------+---+---+---+---+---+

| a| b | c | e | f | g | a| b | c | e | f | g |

+------+---+---+---+---+---+------+---+---+---+---+---+

| 1| | | | | | 1| | | | | |

+------+---+---+---+---+---+------+---+---+---+---+---+

1 row in set (0.00 sec)

mysql> update abc,def set abc.b='hello',def.c='brother' where abc.a=def.a

Query OK, 2 rows affected, 2 warnings (0.00 sec)

Rows matched: 2 Changed: 2 Warnings: 2

mysql>select * from abc,def

+------+------+---+---+---+---+------+---+------+---+---+---+

| a| b| c | e | f | g | a| b | c| e | f | g |

+------+------+---+---+---+---+------+---+------+---+---+---+

| 1| hell | | | | | 1| | brot | | | |

+------+------+---+---+---+---+------+---+------+---+---+---+

1 row in set (0.00 sec)

如果你用其他数据库，也可以try 下。不知道支持不。

---