为已有数据的hotel表新增roomNumber列后如何将值填充到id=1起的已有行

INSERT语句的作用是往数据表中新增行，你需要修改已有行的字段值，要使用UPDATE语句实现需求，操作步骤如下：

步骤1：清理错误插入的多余行 #

你之前执行INSERT新增的行属于冗余数据，需要先删除。由于你新增roomNumber列后，原有旧行的roomNumber字段默认是空值，而新插入的冗余行只有roomNumber有值、原有表的其他字段均为空，你可以先执行查询确认冗余行：

SELECT * FROM hotel WHERE roomNumber IS NOT NULL;

确认返回结果就是你刚插入的10条冗余数据后，执行删除：

DELETE FROM hotel WHERE roomNumber IS NOT NULL;

步骤2：为原有旧行填充roomNumber值 #

如果你给出的10个值顺序和id从1到10的行一一对应（即id=1填20，id=2填60，以此类推），可以选择以下两种方法操作：

方法1：逐行更新（数据量少时推荐，逻辑简单不易出错）

直接执行对应UPDATE语句即可：

UPDATE hotel SET roomNumber = 20 WHERE id = 1;
UPDATE hotel SET roomNumber = 60 WHERE id = 2;
UPDATE hotel SET roomNumber = 100 WHERE id = 3;
UPDATE hotel SET roomNumber = 20 WHERE id = 4;
UPDATE hotel SET roomNumber = 20 WHERE id = 5;
UPDATE hotel SET roomNumber = 150 WHERE id = 6;
UPDATE hotel SET roomNumber = 50 WHERE id = 7;
UPDATE hotel SET roomNumber = 50 WHERE id = 8;
UPDATE hotel SET roomNumber = 30 WHERE id = 9;
UPDATE hotel SET roomNumber = 50 WHERE id = 10;

方法2：批量关联更新（数据量多时推荐，无需写多条语句）

以MySQL为例，可以用临时映射表的方式批量更新：

UPDATE hotel h
JOIN (
    SELECT 1 AS id, 20 AS room_num UNION ALL
    SELECT 2 AS id, 60 AS room_num UNION ALL
    SELECT 3 AS id, 100 AS room_num UNION ALL
    SELECT 4 AS id, 20 AS room_num UNION ALL
    SELECT 5 AS id, 20 AS room_num UNION ALL
    SELECT 6 AS id, 150 AS room_num UNION ALL
    SELECT 7 AS id, 50 AS room_num UNION ALL
    SELECT 8 AS id, 50 AS room_num UNION ALL
    SELECT 9 AS id, 30 AS room_num UNION ALL
    SELECT 10 AS id, 50 AS room_num
) t ON h.id = t.id
SET h.roomNumber = t.room_num;

如果值不需要和id严格按顺序对应，只是要把这10个值分配到已有10行中，可以用窗口函数匹配实现批量分配，MySQL8.0+版本写法如下：

UPDATE hotel h
JOIN (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM hotel
) h1 ON h.id = h1.id
JOIN (
    SELECT ROW_NUMBER() OVER () AS row_num, num 
    FROM (
        VALUES ROW(20),ROW(60),ROW(100),ROW(20),ROW(20),ROW(150),ROW(50),ROW(50),ROW(30),ROW(50)
    ) t(num)
) t1 ON h1.row_num = t1.row_num
SET h.roomNumber = t1.num;

内容的提问来源于stack exchange，提问作者user16890274