How do I update all MySQL table rows at the same time?
如何同时更新所有MySQL表行？
For example, I have the table:
例如，我有表：
id | ip | port | online_status |
1 | ip1 | port1 | |
2 | ip2 | port2 | |
3 | ip3 | port3 | |
4 | ip4 | port4 | |
5 | ip5 | port5 | |
I'm planning to create cronjob and monitor some servers, but I don't know exactly how to update them all from the table at the same time. What are some examples on how to do that?
我打算创建cronjob并监视一些服务器，但我不确切知道如何从表中同时更新它们。有关如何做到这一点的一些例子？
7 个解决方案
#1
26
Omit the where clause:
省略where子句：
update mytable set
column1 = value1,
column2 = value2,
-- other column values etc
;
This will give all rows the same values.
这将为所有行提供相同的值。
This might not be what you want - consider truncate then a mass insert:
这可能不是你想要的 - 考虑截断然后一个质量插入：
truncate mytable; -- delete all rows efficiently
insert into mytable (column1, column2, ...) values
(row1value1, row1value2, ...), -- row 1
(row2value1, row2value2, ...), -- row 2
-- etc
;
#2
23
update mytable set online_status = 'online'
If you want to assign different values, you should use the TRANSACTION technique.
如果要分配不同的值，则应使用TRANSACTION技术。
#3
7
The default null value for a field is "not null". So you must set it to "null" before you can set that field value for any record to null. Then you can:
字段的默认空值为“not null”。因此，必须先将其设置为“null”，然后才能将任何记录的字段值设置为null。然后你可以：
UPDATE `myTable` SET `myField` = null
#4
0
i just want to add one more way put a condition in where clause which is always true, for all columns
我只是想为所有列添加一个方法，将where子句放在始终为true的子句中
UPDATE tablename SET online_status=0 WHERE 1=1;
and as @bohemian already answered it well, simply just omit the where clause
并且@bohemian已经很好地回答了它，只是省略了where子句
UPDATE tablename SET online_status=0;
#5
0
You can try this,
你可以试试这个，
UPDATE *tableName* SET *field1* = *your_data*, *field2* = *your_data* ... WHERE 1 = 1;
Well in your case if you want to update your online_status to some value, you can try this,
那么在你的情况下，如果你想将你的online_status更新为某个值，你可以尝试这个，
UPDATE thisTable SET online_status = 'Online' WHERE 1 = 1;
Hope it helps. :D
希望能帮助到你。 ：d
#6
0
Just add parameters, split by comma:
只需添加参数，用逗号分隔：
UPDATE tablename SET column1 = "value1", column2 = "value2" ....
see the link also MySQL UPDATE
看到链接还有MySQL UPDATE
#7
-1
UPDATE dummy SET myfield=1 WHERE id>1;
#1
26
Omit the where clause:
省略where子句：
update mytable set
column1 = value1,
column2 = value2,
-- other column values etc
;
This will give all rows the same values.
这将为所有行提供相同的值。
This might not be what you want - consider truncate then a mass insert:
这可能不是你想要的 - 考虑截断然后一个质量插入：
truncate mytable; -- delete all rows efficiently
insert into mytable (column1, column2, ...) values
(row1value1, row1value2, ...), -- row 1
(row2value1, row2value2, ...), -- row 2
-- etc
;
#2
23
update mytable set online_status = 'online'
If you want to assign different values, you should use the TRANSACTION technique.
如果要分配不同的值，则应使用TRANSACTION技术。
#3
7
The default null value for a field is "not null". So you must set it to "null" before you can set that field value for any record to null. Then you can:
字段的默认空值为“not null”。因此，必须先将其设置为“null”，然后才能将任何记录的字段值设置为null。然后你可以：
UPDATE `myTable` SET `myField` = null
#4
0
i just want to add one more way put a condition in where clause which is always true, for all columns
我只是想为所有列添加一个方法，将where子句放在始终为true的子句中
UPDATE tablename SET online_status=0 WHERE 1=1;
and as @bohemian already answered it well, simply just omit the where clause
并且@bohemian已经很好地回答了它，只是省略了where子句
UPDATE tablename SET online_status=0;
#5
0
You can try this,
你可以试试这个，
UPDATE *tableName* SET *field1* = *your_data*, *field2* = *your_data* ... WHERE 1 = 1;
Well in your case if you want to update your online_status to some value, you can try this,
那么在你的情况下，如果你想将你的online_status更新为某个值，你可以尝试这个，
UPDATE thisTable SET online_status = 'Online' WHERE 1 = 1;
Hope it helps. :D
希望能帮助到你。 ：d
#6
0
Just add parameters, split by comma:
只需添加参数，用逗号分隔：
UPDATE tablename SET column1 = "value1", column2 = "value2" ....
see the link also MySQL UPDATE
看到链接还有MySQL UPDATE
#7
-1
UPDATE dummy SET myfield=1 WHERE id>1;