UPDATE rows with values from a table JOIN in Oracle

Example use case: I have a database that contains a table of contacts (contact) and table of e-mail addresses (email), joined on contact.id = email.contact_id. I just found out that Example Conglomerate acquired Osric Publishing’s Oracle consulting business, and so I need to update my contacts database so that all of the Oracle consultants who had @osric.com e-mail addresses now have @example.com e-mail addresses.

How can I change just the affected addresses in the contact database, assuming the username portion of their e-mail addresses remains the same?

The first time I tried it, I used this query:

UPDATE contacts.email ce
SET email = (
    SELECT REPLACE(email, '@osric.com', 'example.com')
    FROM contacts.email e 
    INNER JOIN contacts.contact c ON e.contact_id = c.id
    WHERE c.dept_id = 'oracle' 
    AND e.email LIKE '%@osric.com'
) WHERE EXISTS (
    SELECT 1
    FROM contacts.email e 
    INNER JOIN contacts.contact c ON e.contact_id = c.id
    WHERE c.dept_id = 'oracle' 
    AND e.email LIKE '%@osric.com'
);

That query returned an error message:
ORA-01427: single-row subquery returns more than one row

That makes sense, I’m trying to set a single e-mail address to the entire result-set of e-mail addresses.

What I want is called a correlated subquery. “A correlated subquery conceptually is evaluated once for each row processed by the parent statement.” (Using Subqueries). I revised my query:

UPDATE contacts.email ce
SET email = (
    SELECT REPLACE(email, '@osric.com', '@example.com')
    FROM contacts.email e 
    INNER JOIN contacts.contact c ON e.contact_id = c.id
    WHERE c.dept_id = 'oracle' 
    AND e.email LIKE '%@osric.com'
    AND ce.contact_id = e.contact_id
) WHERE EXISTS (
    SELECT 1
    FROM contacts.email e 
    INNER JOIN contacts.contact c ON e.contact_id = c.id
    WHERE c.dept_id = 'oracle' 
    AND e.email LIKE '%@osric.com'
    AND ce.contact_id = e.contact_id
);

The key is this portion of the WHERE clause:
ce.contact_id = e.contact_id
That joins the rows from the parent UPDATE query with the rows from the SELECT subquery.

When I ran the SELECT query on its own it returned 163 rows. However, when I ran the UPDATE query it updated 210 rows.

Uh-oh. Rollback!

Here’s the SELECT query I ran

SELECT REPLACE(email, '@osric.com', '@example.com')
FROM contacts.email e 
INNER JOIN contacts.contact c ON e.contact_id = c.id
WHERE c.dept_id = 'oracle' 
AND e.email LIKE '%@osric.com'

Users can have multiple email addresses but the UPDATE query is matching on contact ID in the WHERE clause. This would update other addresses associated that contact ID has as well.

Here’s the query I used. Note the last line, an addition to the WHERE clause at the end, specifying that only rows matching @osric.com should be updated:

UPDATE contacts.email ce
SET email = (
    SELECT REPLACE(email, '@osric.com', '@example.com')
    FROM contacts.email e 
    INNER JOIN contacts.contact c ON e.contact_id = c.id
    WHERE c.dept_id = 'oracle' 
    AND e.email LIKE '%@osric.com'
    AND ce.contact_id = e.contact_id
) WHERE EXISTS (
    SELECT 1
    FROM contacts.email e 
    INNER JOIN contacts.contact c ON e.contact_id = c.id
    WHERE c.dept_id = 'oracle' 
    AND e.email LIKE '%@osric.com'
    AND ce.contact_id = e.contact_id
)
AND ce.email LIKE '%@osric.com';