mysql中怎样批量修改

UPDATE users
SET status = CASE id
    WHEN 1 THEN 'active'
    WHEN 2 THEN 'inactive'
    WHEN 3 THEN 'pending'
    -- ... 其他id和对应的状态
END;

UPDATE users u
JOIN (
    SELECT user_id, SUM(amount) as total_amount
    FROM orders
    GROUP BY user_id
) o ON u.id = o.user_id
SET u.total_amount = o.total_amount;

CREATE TEMPORARY TABLE temp_user_status AS
SELECT id, new_status
FROM (
    -- 这里可以是任何复杂的查询来生成需要更新的数据
    SELECT id, 'new_status_value' as new_status
    FROM users
    WHERE ...
) AS subquery;

UPDATE users u
JOIN temp_user_status tus ON u.id = tus.id
SET u.status = tus.new_status;

DROP TEMPORARY TABLE temp_user_status;

DELIMITER //

CREATE PROCEDURE BatchUpdateUserStatus()
BEGIN
    -- 这里可以放置批量更新的逻辑
    UPDATE users
    SET status = 'new_status_value'
    WHERE ...
END //

DELIMITER ;

-- 调用存储过程
CALL BatchUpdateUserStatus();