Truy vẫn tìm những email bị trùng trong bảng Khách hàng (customer)
SELECT id, email, phone, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rnFROM customerWHERE shop_id = 106AND email IS NOT NULLAND email != ''AND email IN (SELECT emailFROM customerWHERE shop_id = 106GROUP BY emailHAVING COUNT(*) > 1)
Thêm ký tự * đằng sau dữ liệu Email trùng lặp
UPDATE customer AS c
JOIN (
SELECT
id,
email,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM customer
WHERE shop_id = 106
AND email IS NOT NULL
AND email != ''
AND email IN (
SELECT email
FROM customer
WHERE shop_id = 106
GROUP BY email
HAVING COUNT(*) > 1
)
) AS d ON c.id = d.id
SET c.email = CONCAT(c.email, '*')
WHERE d.rn > 1;
Nhận xét
Đăng nhận xét