MySQL 怎么一次性把多个 值 按照列填入 某个表;

Question

比如这个表:

+----+------------+--------+------+----------------+----------+
| id | 学籍编号   | 姓名   | 性别 | 档案           | 职务     |
+----+------------+--------+------+----------------+----------+
|  3 | 8594320350 | 高清   | 女   | 沂水县实验中学 | 班长     |
|  4 | 3802991271 | 马玉昭 | 男   | 沂水县实验中学 | 班长     |
|  5 | 3231995612 | 段升云 | 女   | 沂水县实验中学 | 体育委员 |
|  6 | 4751004553 | 蔡瑶   | 女   | 沂水县实验中学 |          |
|  7 | 4059036238 | 谭维维 | 女   | 沂水县实验中学 |          |
+----+------------+--------+------+----------------+----------+

我想一次性把 学籍编号 改成 11111,22222,33333,44444,55555,
按照我现在的水平,只能这样修改:

UPDATE c200402 SET 学籍编号 = 11111 WHERE id=3 ;
UPDATE c200402 SET 学籍编号 = 22222 WHERE id=4 ;
UPDATE c200402 SET 学籍编号 = 33333 WHERE id=5 ;
UPDATE c200402 SET 学籍编号 = 44444 WHERE id=6 ;
UPDATE c200402 SET 学籍编号 = 55555 WHERE id=7 ;

请问能直接把他们填入吗?

要是用excel的vba的话:
可以这样:

dim arr=Array(11111,22222,33333,44444,55555)
rang("B2").Resize(5, 1)=Application.Transpose(arr)

虽然大家可能没接触过vba,但是大体什么意思也都看得懂,劳烦解答,谢谢了!

Answer 1

I don’t know if this method suits your requirements. You can put the new id and number in b table b(id, number). Looking at your example, this corresponding data should be available. Then
`update a, b
set a.number = b.number
where a.id = b.id;`
Regardless of other practical issues, this should be fine.

Answer 2

In fact, I want to implement multiple operations in one SQL statement and complete multiple update operations in one statement, right?