请求编写SQL Update语句移除邮箱字段中的指定额外邮箱

Got it, let's work through this. First, I’ll assume your email field stores multiple addresses separated by a common delimiter (like , — comma plus space, the most common setup). If your delimiter is different (e.g., just , or ;), adjust the patterns accordingly.

Below are tailored solutions for the most popular databases, plus key precautions to avoid mistakes.

1. MySQL/MariaDB (Version 8.0+/10.0.5+) #

We’ll use REGEXP_REPLACE to target the exact unwanted emails and clean up leftover delimiters:

UPDATE your_table_name
SET email_field = TRIM(',' FROM REGEXP_REPLACE(
    REGEXP_REPLACE(email_field, 'fundraiser@xyz.com,? ', ''),
    'gorilla@xyz.com,? ', ''
))
WHERE email_field LIKE '%fundraiser@xyz.com%' 
   OR email_field LIKE '%gorilla@xyz.com%';

Quick Notes: #

• The ,? matches an optional comma followed by a space, so it handles cases where the target email is in the middle or end of the list.
• TRIM(',' FROM ...) fixes any leading/trailing commas left after removal.
• The WHERE clause only updates rows that actually have the unwanted emails — way more efficient than updating every row.

2. PostgreSQL #

PostgreSQL’s REGEXP_REPLACE works similarly, with a 'g' flag to catch all occurrences (in case the same email was added multiple times):

UPDATE your_table_name
SET email_field = TRIM(BOTH ',' FROM REGEXP_REPLACE(
    REGEXP_REPLACE(email_field, 'fundraiser@xyz.com(, )?', '', 'g'),
    'gorilla@xyz.com(, )?', '', 'g'
))
WHERE email_field LIKE '%fundraiser@xyz.com%' 
   OR email_field LIKE '%gorilla@xyz.com%';

3. SQL Server #

For SQL Server, splitting and re-aggregating the list is more robust for messy delimiters (like extra spaces):

WITH SplitEmails AS (
    SELECT 
        id, -- Replace with your table's primary key
        TRIM(value) AS email
    FROM your_table_name
    CROSS APPLY STRING_SPLIT(email_field, ',')
    WHERE TRIM(value) NOT IN ('fundraiser@xyz.com', 'gorilla@xyz.com')
)
UPDATE t
SET email_field = (
    SELECT STRING_AGG(email, ', ')
    FROM SplitEmails se
    WHERE se.id = t.id
)
FROM your_table_name t
WHERE t.email_field LIKE '%fundraiser@xyz.com%' 
   OR t.email_field LIKE '%gorilla@xyz.com%';

Quick Note: #

• Replace id with your table’s actual primary key to make sure split emails are linked back to the correct original row.

Critical Precaution #

Always test before updating! Run a SELECT query first to verify the cleaned output matches what you want. For example, in MySQL:

SELECT 
    email_field,
    TRIM(',' FROM REGEXP_REPLACE(
        REGEXP_REPLACE(email_field, 'fundraiser@xyz.com,? ', ''),
        'gorilla@xyz.com,? ', ''
    )) AS cleaned_email
FROM your_table_name
WHERE email_field LIKE '%fundraiser@xyz.com%' 
   OR email_field LIKE '%gorilla@xyz.com%';

This lets you confirm no valid emails get accidentally removed before making permanent changes.

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