背景信息:我们有一个社区房间，可以通过窗帘将其分成两半。过去，当一个团体需要整个房间时，我们会放入 2 个条目，每半个房间一个...但是我们修改了软件 (MRBS)，现在有 3 个房间(完整{1}，壁橱边{2 }, 和 Kitchen Side{3})，并且软件会检查您不能在整个房间已经预订时保留部分房间，反之亦然。然而，我们有很多旧的“满房”预订都是通过双方预订来完成的。因此，当 2 和 3 相同时，我需要将其中一个预订移至 1 并删除另一个。

所以我有一个表格，例如:

id  room_id  start_time   name
1   2        13:00        Meeting
2   2        15:00        Meeting
3   3        15:00        Meeting
4   3        13:00        Storytime

我想遍历表格，当房间 2 和 3 同时具有相同名称的条目时，我想将房间 2 的 room_id 更改为 1 并删除房间 3 的条目。所以在上面示例条目 2 将被修改，条目 3 将被删除。

我相当确定这需要是两个单独的查询； EG 首先将 2 的所有 room_id 更改为 1，然后作为单独的查询，比较房间 1 和 3 并删除 3 上的条目。

我认为将房间 2 换成房间 1 很接近:

UPDATE `mrbs_entry`
JOIN `mrbs_entry` AS `other_side` ON `other_side.room_id` = '3'
    AND `other_side.name` = `mrbs_entry.name`
    AND `other_side.start_time` = `mrbs_entry.start_time`
    AND `other_side.id` != `mrbs_entry.id`
SET `mrbs_entry.room_id` = '1'
WHERE (`mrbs_entry.room_id` = '2' AND `mrbs_entery.id` IN(92437,92438,92442,92443,92470,92471,92477,92478,92489,89462,92496,90873))

但是我得到一个#1054 - Unknown column 'mrbs_entry.room_id' in 'field list' 错误

注意:IN(*) 位是将其限制为几个测试条目，以确保它确实按预期工作。

最佳答案

方法 1 - 带临时表的存储过程

如果您准备使用存储过程和临时表，这似乎是最简单的方法:

CREATE PROCEDURE sp_sanitize_mrbs()
BEGIN
    DROP TEMPORARY TABLE IF EXISTS mrbs_to_sanitize;
    CREATE TEMPORARY TABLE mrbs_to_sanitize (
      id int auto_increment primary key,
      room2_id int,
      room3_id int);

    -- "I want to go through the table, and when room 2 & 3 both have
    -- entries at the same time and with the same name I want to..."
    INSERT INTO mrbs_to_sanitize (room2_id, room3_id)
    SELECT m1.id, m2.id
    FROM mrbs_entry m1
    CROSS JOIN mrbs_entry m2
    WHERE m1.start_time = m2.start_time
      AND m1.name = m2.name
      AND m1.room_id = 2
      AND m2.room_id = 3;

    -- ...change room 2's room_id to 1
    UPDATE mrbs_entry me
    JOIN mrbs_to_sanitize mts
    ON me.id = mts.room2_id
    SET me.room_id = 1;

    -- "...and delete the entry for room 3."
    DELETE me
    FROM mrbs_entry me
    JOIN mrbs_to_sanitize mts
    ON me.id = mts.room3_id;
END//

-- ...
-- The Stored Procedure can now be called any time you like:
CALL sp_sanitize_mrbs();

参见 SQL Fiddle Demo - using a Stored Procedure

方法 2 - 没有存储过程

下面的“技巧”稍微复杂一些，但应该在不使用存储过程、临时表或变量的情况下完成:

-- "I want to go through the table, and when room 2 & 3 both have
-- entries at the same time and with the same name I want to..."

-- "...change room 2's room_id to 1"
UPDATE mrbs_entry m1
CROSS JOIN mrbs_entry m2
-- temporarily mark this row as having been updated
SET m1.room_id = 1, m1.name = CONCAT(m1.name, ' UPDATED')
WHERE m1.start_time = m2.start_time
  AND m1.name = m2.name
  AND m1.room_id = 2
  AND m2.room_id = 3;

-- "...and delete the entry for room 3."
DELETE m2 FROM mrbs_entry m1
CROSS JOIN mrbs_entry m2
WHERE m1.start_time = m2.start_time
  AND m1.name = CONCAT(m2.name, ' UPDATED')
  AND m1.room_id = 1
  AND m2.room_id = 3;

-- now remove the temporary marker to restore previous value
UPDATE mrbs_entry
SET name = LEFT(name, CHAR_LENGTH(name) - CHAR_LENGTH(' UPDATED'))
WHERE name LIKE '% UPDATED';

方法二的解释

第一个查询更新房间号。但是，正如您提到的，我们需要在单独的查询中执行删除。由于我没有对您的数据做出任何假设，因此在修改数据后重新查询以获得相同结果的一种安全方法是引入一个“标记”来临时指示更新更改了哪一行。 在上面的示例中，此标记是 'UPDATED '，但您可能希望选择更可能永远不会用于任何其他目的的标记，例如随机的字符序列。如果需要，它也可以移动到不同的字段。然后可以执行删除，最后需要删除标记以恢复原始数据。

参见 SQL Fiddle demo - without Stored Procedure .

关于mysql - 当操作影响集合时更新和删除一组行，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/34773830/