'How to update all MySQL table rows at the same time?

How do I update all MySQL table rows at the same time?

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?

mysql


Solution 1:[1]

Omit the where clause:

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
;

Solution 2:[2]

update mytable set online_status = 'online'

If you want to assign different values, you should use the TRANSACTION technique.

Solution 3:[3]

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:

UPDATE `myTable` SET `myField` = null

Solution 4:[4]

UPDATE dummy SET myfield=1 WHERE id>1;

Solution 5:[5]

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,

UPDATE thisTable SET online_status = 'Online' WHERE 1 = 1;

Hope it helps. :D

Solution 6:[6]

just use UPDATE query without condition like this

 UPDATE tablename SET online_status=0;

Solution 7:[7]

Just add parameters, split by comma:

UPDATE tablename SET column1 = "value1", column2 = "value2" ....

see the link also MySQL UPDATE

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Bohemian
Solution 2 taskinoor
Solution 3 inorganik
Solution 4 Rahul Sharma
Solution 5 Kent Allen Sison
Solution 6
Solution 7 Saravanan Nandhan

Related Questions