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';

One thought on “UPDATE rows with values from a table JOIN in Oracle”

  1. I just tried a similar UPDATE query in MySQL that produced an error message:

    UPDATE email oe
    SET address = (
        SELECT REPLACE(address,'@osric.com','@example.com')
        FROM email ie 
        WHERE ie.address LIKE '%@osric.com'
    ) WHERE EXISTS (
        SELECT 1
        FROM email ie 
        WHERE address LIKE '%@osric.com'
        AND ie.id = oe.id
    )
    AND oe.email LIKE '%@osric.com';

    The error message:
    ERROR 1093 (HY000): You can’t specify target table ‘oe’ for update in FROM clause

    A StackOverflow answer (see MySQL Error 1093 – Can’t specify target table for update in FROM clause) explains that you can’t update the same table you use in a SELECT subquery.

    Based on the suggestions in the answer there, I updated the query to the following, which worked:

    UPDATE email AS oe
    INNER JOIN (
        SELECT id, REPLACE(address, '@osric.com', '@example.com') AS address 
        FROM email
    ) AS ie ON oe.id = ie.id
    SET oe.address = ie.address
    WHERE address LIKE '%@osric.com';

Leave a Reply

Your email address will not be published. Required fields are marked *