CASE expression with Derived Table: Alternative combo for multiple row UPDATE.

In this blog post, we will leverage the CASE expression and a Derived Table for updating multiple rows within the same UPDATE statement.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

A Handy Find

This blog post stems from a fantastic PDF I happened upon on the web (Can’t quite remember where I ran into it). MySQL Notes for Professionals is a jam-packed PDF of useful knowledge, tips, and pointers. The section on bulk updates with the CASE expression grabbed my attention. Salivating at the opportunity to learn something new and grow, I found a use inline with my own interest and needs, so here we are. Be sure and check it out through the link provided.

Present Data Set

We are working with this table and data. It represents a mock pipe/manufacturer/pipe grade system complete with pipe name, manufacturer, and a grade type for each:

Goal And How-To

We need to change the pipe_grade column value on a per manufacturer basis as follows:

Company 1 = X75A
Company 2 = X75B 
Company 3 = X75C
PipeCo = C75X

Of course, these 4 individual UPDATE statements can take care of this:

UPDATE asset_staging
SET pipe_grade = ‘X75A’
WHERE manufacturer = ‘Company 1’;
UPDATE asset_staging
SET pipe_grade = ‘X75B’
WHERE manufacturer = ‘Company 2’;
UPDATE asset_staging
SET pipe_grade = ‘X75C’
WHERE manufacturer = ‘Company 3’;
UPDATE asset_staging
SET pipe_grade = ‘C75X’
WHERE manufacturer = ‘PipeCo’;

Photo by Chris Ried on Unsplash

Yet, a CASE expression inside the UPDATE command is also an option.
Here is how that looks:

Wait a minute. Shouldn’t that work?

I thought:

WHERE manufacturer IN (SELECT DISTINCT manufacturer FROM asset_staging);

was the same as:

WHERE manufacturer IN (‘Company 1’, ‘Company 2’,’Company 3',’PipeCo’);

?

I’ll dig into the issue by first running that SELECT query,

mysql> SELECT DISTINCT manufacturer FROM asset_staging;
 + — — — — — — — +
 | manufacturer  |
 + — — — — — — — +
 | Company 2     |
 | PipeCo        |
 | Company 1     |
 | Company 3     |
 + — — — — — — — +
 4 rows in set (0.00 sec)

The query does return the same number of rows, with the same names. The ordering of names is different though.

But that’s fine too. The IN() predicate column or expression only has to be in the list of supplied values.

Turns out, MySQL is ‘slapping our hand’ for referencing the asset_staging table in the subquery during an UPDATE of it (the asset_staging table).

How about just SELECT from that subquery then ‘feed’ the result set to the UPDATE right?
Like this:

mysql> SELECT * FROM (SELECT DISTINCT manufacturer FROM asset_staging);
ERROR 1248 (42000): Every derived table must have its own alias

Hmmm…
Turns out we can’t do that either. The subquery in the FROM clause must be treated differently.
See below.

Derived Table

Let’s come to understand Derived Tables with a description and explanation from the Official Documentation in Section 13.2.10.8.

A derived table is a subquery in a SELECT statement FROM clause:

In this sense, Derived tables act like tables. Therefore they require an ALIAS since all tables must be named in the FROM clause.

First, let’s get this SELECT on the DISTINCT manufacturer column working.

mysql> SELECT * FROM (SELECT DISTINCT manufacturer FROM asset_staging) AS my_der_tbl;
 + — — — — — — — +
 | manufacturer  |
 + — — — — — — — +
 | Company 2     |
 | PipeCo        |
 | Company 1     |
 | Company 3     |
 + — — — — — — — +
 4 rows in set (0.00 sec)

By providing the AS my_der_tbl table ALIAS that worked.
We could even ALIAS the column from the Derived Table and the outer query will recognize and return it no problem:

mysql> SELECT maker FROM (SELECT DISTINCT manufacturer AS maker FROM asset_staging) as my_der_tbl;
 + — — — — — -+
 | maker      |
 + — — — — — -+
 | Company 2  |
 | PipeCo     |
 | Company 1  |
 | Company 3  |
 + — — — — — -+
 4 rows in set (0.00 sec)

For this immediate need, the below UPDATE accomplishes the goal:

∗Note:
Remember, per the docs, AS table_name is a mandatory clause in the Derived Table syntax.

Updated Data and Final Thoughts

Let’s verify the results by selecting those previous 3 columns:

All updates are successful.

I hope through this blog post, you now have and idea how to use MySQL Derived Tables. For this example, pairing them with the CASE expression, allowed updating multiple rows within the same table in one UPDATE statement.

I would love to hear your thoughts on Derived Tables best practices and implementations. Although new to me, I am excited to continue to explore, learn, and employ them as I discover use cases they would serve best.

I have included links to informative and interesting articles I found while searching and studying Derived Tables. Feel free to read those as well.

• Difference Between A Subquery And A Derived Table
• An Essential Guide To MySQL Derived Table

Visit the official MySQL 5.7 On-line Manual for more information.

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage!
Be sure and visit the “Best Of” page for a collection of my best blog posts while you are there!

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

Originally published at joshuaotwell.com on March 21, 2018.

✉️ Subscribe to CodeBurst’s once-weekly Email Blast, 🐦 Follow CodeBurst on Twitter, view 🗺️ The 2018 Web Developer Roadmap, and 🕸️ Learn Full Stack Web Development.