使用先前的值MYSQL更新重复行

-- 创建示例表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    status VARCHAR(50)
);

-- 插入示例数据
INSERT INTO users (email, status) VALUES
('user1@example.com', 'active'),
('user2@example.com', 'inactive'),
('user1@example.com', 'pending');

-- 更新重复行的status字段
UPDATE users
SET status = 'updated'
WHERE email IN (
    SELECT email
    FROM users
    GROUP BY email
    HAVING COUNT(*) > 1
);

UPDATE users
SET status = 'updated'
WHERE email IN (
    SELECT email
    FROM users
    GROUP BY email
    HAVING COUNT(*) > 1
)
AND id NOT IN (
    SELECT MIN(id)
    FROM users
    GROUP BY email
);

START TRANSACTION;

UPDATE users
SET status = 'updated'
WHERE email IN (
    SELECT email
    FROM users
    GROUP BY email
    HAVING COUNT(*) > 1
);

COMMIT;