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.<\/p>\n
How can I change just the affected addresses in the contact database, assuming the username portion of their e-mail addresses remains the same?
\n<\/p>\n
The first time I tried it, I used this query:<\/p>\n
UPDATE contacts.email ce\r\nSET email = (\r\n SELECT REPLACE(email, '@osric.com', 'example.com')\r\n FROM contacts.email e \r\n INNER JOIN contacts.contact c ON e.contact_id = c.id\r\n WHERE c.dept_id = 'oracle' \r\n AND e.email LIKE '%@osric.com'\r\n) WHERE EXISTS (\r\n SELECT 1\r\n FROM contacts.email e \r\n INNER JOIN contacts.contact c ON e.contact_id = c.id\r\n WHERE c.dept_id = 'oracle' \r\n AND e.email LIKE '%@osric.com'\r\n);<\/code><\/pre>\nThat query returned an error message:
\nORA-01427: single-row subquery returns more than one row<\/code><\/p>\nThat makes sense, I’m trying to set a single e-mail address to the entire result-set of e-mail addresses.<\/p>\n
What I want is called a correlated subquery<\/em>. “A correlated subquery conceptually is evaluated once for each row processed by the parent statement.” (Using Subqueries<\/a>). I revised my query:<\/p>\nUPDATE contacts.email ce\r\nSET email = (\r\n SELECT REPLACE(email, '@osric.com', '@example.com')\r\n FROM contacts.email e \r\n INNER JOIN contacts.contact c ON e.contact_id = c.id\r\n WHERE c.dept_id = 'oracle' \r\n AND e.email LIKE '%@osric.com'\r\n AND ce.contact_id = e.contact_id\r\n) WHERE EXISTS (\r\n SELECT 1\r\n FROM contacts.email e \r\n INNER JOIN contacts.contact c ON e.contact_id = c.id\r\n WHERE c.dept_id = 'oracle' \r\n AND e.email LIKE '%@osric.com'\r\n AND ce.contact_id = e.contact_id\r\n);<\/code><\/pre>\nThe key is this portion of the WHERE clause:
\nce.contact_id = e.contact_id<\/code>
\nThat joins the rows from the parent UPDATE query with the rows from the SELECT subquery.<\/p>\nWhen I ran the SELECT query on its own it returned 163 rows. However, when I ran the UPDATE query it updated 210 rows.<\/p>\n
Uh-oh. Rollback!<\/p>\n
Here’s the SELECT query I ran<\/p>\n
SELECT REPLACE(email, '@osric.com', '@example.com')\r\nFROM contacts.email e \r\nINNER JOIN contacts.contact c ON e.contact_id = c.id\r\nWHERE c.dept_id = 'oracle' \r\nAND e.email LIKE '%@osric.com'<\/code><\/pre>\nUsers 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.<\/p>\n
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:<\/p>\n
UPDATE contacts.email ce\r\nSET email = (\r\n SELECT REPLACE(email, '@osric.com', '@example.com')\r\n FROM contacts.email e \r\n INNER JOIN contacts.contact c ON e.contact_id = c.id\r\n WHERE c.dept_id = 'oracle' \r\n AND e.email LIKE '%@osric.com'\r\n AND ce.contact_id = e.contact_id\r\n) WHERE EXISTS (\r\n SELECT 1\r\n FROM contacts.email e \r\n INNER JOIN contacts.contact c ON e.contact_id = c.id\r\n WHERE c.dept_id = 'oracle' \r\n AND e.email LIKE '%@osric.com'\r\n AND ce.contact_id = e.contact_id\r\n)\r\nAND ce.email LIKE '%@osric.com';<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"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 … Continue reading UPDATE rows with values from a table JOIN in Oracle<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[282],"tags":[274,23],"class_list":["post-1576","post","type-post","status-publish","format-standard","hentry","category-oracle-2","tag-oracle","tag-sql"],"_links":{"self":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/1576","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/comments?post=1576"}],"version-history":[{"count":9,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/1576\/revisions"}],"predecessor-version":[{"id":1586,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/1576\/revisions\/1586"}],"wp:attachment":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/media?parent=1576"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/categories?post=1576"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/tags?post=1576"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}