Oh, Joy! More JOINs!

JOINing Tables in INSERT, UPDATE, and DELETE Queries

Aug 17, 2007
0 comments

Submitted By: Justin Stanley
Filed Under:

• MS SQL
• MySQL
• Oracle
• PostgreSQL

In our last SQL post, we talked about the three basic types of JOINs enterprising young SQL junkies might use to gather data from different tables in their databases.

"Enterprising..." Oh, how I crack myself up some times. Seriously. Don't make me explain why that's funny.

Anyway, in between Star Trek-related examples, we blabbed on and on about INNER JOINs, three kinds of OUTER JOINs, and the almost completely useless CROSS JOIN. You might remember, though, that every one of those brilliant examples involved querying existing data with SELECT statements. That's fine and dandy. After all, that's also how you'll use those JOINs about 98% of the time. However, JOINs aren't just confined to SELECT statements...

No, sir. You can use JOINs in your INSERT, UPDATE, and DELETE statements, too. Sit back and watch.
First, let's resurrect our sample tables from the last post. If you'll recall, we had a STARSHIP table that contained the following data:

And an OFFICER table that looked a little something like this:

So let's have some fun. First, we're going to add another record to our OFFICER table.

Let's say that we have a dashing new OFFICER who we want to assign to the USS Justinian. Star Fleet HR gave us the info we need for the firstname, lastname, and rank fields (Justin, Stanley, and Grand Poobah, respectively), but we still need to know the right value to insert into the currentshipid field.

One way to go about that would be to simply look it up first by throwing a SELECT statement at our STARSHIP table, writing down the shipid value that corresponds to the USS Justinian, then manually adding that to our normal INSERT statement:

INSERT INTO officer 

    (firstname, lastname, rank, currentshipid)

VALUES

    ('Justin', 'Stanley', 'Grand Poobah', 4)

Obviously, that works just fine and you'll probably end up doing that most of the time. But damn it, Jim! This post is about using JOINs (or will be eventually...), so that's what we're bloody hell gonna do. Before we get to that part, though, we need to get familiar with the INSERT...SELECT syntax.

So let's write that query. The first part looks like your regular ol' INSERT statement. You'll notice, though, that the syntax changes a bit right after we specify the column names of the destination table:

INSERT INTO officer

    (firstname, lastname, rank, currentshipid)

SELECT 'Justin', 'Stanley', 'Grand Poobah', s.shipid

    FROM starship s

    WHERE s.name = 'Justinian'

(Note: I didn't need to specify a value for the officerid column because we set it up as an IDENTITY field that'll autoincrement when new records are added when the table was initially created.)

Almost seems to overcomplicate things, doesn't it? Maybe you thought that you could get away with something like this instead:

INSERT INTO officer

    (firstname, lastname, rank, currentshipid)

VALUES

    ('Justin', 'Stanley', 'Grand Poobah', 

        (SELECT shipid FROM starship WHERE name = 'Justinian')

    )

A lot of people probably try that one first then get pissed when it doesn't work. I'm one of them. Thing you have to remember is that the SELECT statement doesn't necessarily return a scalar value, but that's what the INSERT statement (written in this format) requires. Even if you write the SELECT part so that it only returns one record (using TOP, for instance), this query will still poo itself.

And that brings us to a big warning: when you're using the INSERT...SELECT statement, be 100% certain that the SELECT part only returns a single row. Otherwise, there's a pretty good chance that you'll end up INSERTing more rows than you intended.

For example, what if I was trying to assign this new officer to Picard's Enterprise instead? If I just changed the WHERE statement in my query to 'WHERE s.name = 'Enterprise',' I'd be in for a rude awakening. That query would return results for both ships named Enterprise in my table and, therefore, would insert two rows into the OFFICER table with my new guy's info, one with shipid = 1 and another with shipid = 2 (the shipid's for the two ships named 'Enterprise' in my STARSHIP table).

So let's get to the JOIN example. Let's say that we have another new OFFICER to add to the table. Again, Star Fleet HR gives us his firstname, lastname, and rank ('Luke', 'Skoubo', 'Junior Lackey'), but this time they also tell us that he's to serve under Grand Poobah Stanley.

Well, again, we could just use a SELECT statement to find the currentshipid for Stanley's record and use that to create your INSERT. Boring. We're going to use a JOIN so we only have to write one query and don't have to bother fishing through our desk for a pen and Post-It Note:

INSERT INTO officer

    (firstname, lastname, rank, currentshipid)

SELECT 'Luke', 'Skoubo', 'Junior Lackey', s.shipid

    FROM starship s

    INNER JOIN officer o

    ON s.shipid = o.currentshipid

    WHERE o.lastname = 'Stanley'

Simple, eh? Again, though, just have to make sure that the SELECT part only pulls a single record so you don't end up INSERTing more than you expected.

UPDATEs work pretty much the same way and, frankly, I can think of many more situations in which I've used a JOIN in UPDATEs than INSERTs.

For example, what if Star Fleet decided to implement some cost-cutting measures by decommissioning the old Constitution class Enterprise and transferring all it's personnel over to... oh, what's it called? That one ship that Grand Poobah Stanley runs?

As the Star Fleet DBA, we need to ensure that the data in the OFFICER table reflects that change and adjust our currentshipid fields accordingly:

UPDATE o

SET o.currentshipid = 

    (SELECT currentshipid 

    FROM officer 

    WHERE lastname = 'Stanley')

FROM starship s  

INNER JOIN officer o

    ON s.shipid = o.currentshipid

WHERE s.registry = 'NCC-1701'

Now Kirk and Spock are Stanley's minions. Like the INSERT example, I still had to be careful that the subquery I used to find Stanley's currentshipid only returned on row. However, unlike that previous query, wacky things won't happen if my subquery returns multiple results. Instead, it'll just fail altogether with a warning that it can only return a single record if I expect things to actually work.

Finally, a DELETE example. It's very similar to the others, but I find it a little counter-intuitive in its redudancy. With the DELETE...JOIN statement, you have to list the table twice using the FROM statement to begin the JOIN. Check out the example.

Let's say that the Reliant is destroyed while Chekov and Khaaaaaan are both still on board. Star Fleet, anxious to sweep that dirty little hijacking under the rug, asks you to delete all records of OFFICERs assigned to that boat at the time:

DELETE officer

FROM officer o

INNER JOIN starship s

    ON s.shipid = o.currentshipid

WHERE s.name = 'Reliant'

You might try something like "DELETE FROM officer INNER JOIN...", but you'll faily miserably. Sorry, but I didn't write the language or standards.

Well, I don't know about you, but that's about all the JOINing I can take for awhile. As always, happy to hear any questions, comments, or suggestions.