In SQL, how do I use the results of a SELECT to perform row-specific UPDATEs?

June 1, 2009 8:37 AM

In SQL, how do I use the results of a SELECT to perform row-specific UPDATEs?

I'm looking to slightly denormalize a relatively normalized set of tables, namely myclasses, myusers, and a many-to-many table called myclassestomyusers, by putting the name of the instructor (in "LASTNAME, Firstname" format) back into an empty column named Instructor in myclasses.

Note: This is a toy example of a vendor app and database. Yeah, I know denormalizing is naughty. It is what is required here, so that's that.

I have a nice little command which does the appropriate JOINS and spits out instructor names in the format required. However, I do not know how to appropriately weld this to an UPDATE:

SELECT UPPER(myusers.lastname) + ', ' + myusers.firstname AS InstName
FROM myclasses
INNER JOIN myclassestomyusers
ON myclasses.classid = myclassestomyusers.classid
INNER JOIN myusers
ON myclassestomyusers.userid = myusers.userid

UPDATE myclasses
SET myclasses.Instructor = ???what???

Obviously, I can do this programmatically. I'd like to do it at a stroke: a SQL command or two, partially to know it can be done, but also for my own learning experience.

Additionally, what is this technique called? I've flipped through Joe Celko's SQL Puzzles looking for something relevant but have found nothing appropriate (or have been unable to recognize it).

posted by adipocere to Computers & Internet (6 answers total)

According to the MySQL docs, you ought to be able to do it like this (I don't have a MySQL instance to test on right now, so bear with me):

UPDATE myclasses, myclassestomyusers, myusers
SET myclasses.Instructor = UPPER(myusers.lastname) + ', ' + myusers.firstname
WHERE myclasses.classid = myclassestomyusers.classid
AND myclassestomyusers.userid = myusers.userid

posted by uncleozzy at 8:51 AM on June 1, 2009

Best answer: (You could probably also do it like this:

UPDATE myclasees
SET myclasses.Instructor = UPPER(myusers.lastname) + ', ' + myusers.firstname
FROM myclasses INNER JOIN myclassestomyusers
ON myclasses.classid = myclassestomyusers.classid
INNER JOIN myusers
ON myclassestomyusers.userid = myusers.userid

although I'm a little less sure of that particular syntax in MySQL.)
posted by uncleozzy at 8:55 AM on June 1, 2009

Response by poster: Ah, I'm using MS-SQL for this one. (Again, vendor limitation). The second command works; the first does not. I believe MS-SQL does not like the multiple tables for the UPDATE. A quick check suggests Oracle does not like it, either.

It looks like FROM is the concept I have been missing. Thank you!
posted by adipocere at 9:06 AM on June 1, 2009

I don't know why I assumed you were using MySQL. (I actually tested the second example on MS SQL.) Glad it worked for you.
posted by uncleozzy at 9:10 AM on June 1, 2009

Details here: Specifying the Data Source by Using the FROM Clause
posted by SNACKeR at 10:23 AM on June 1, 2009

Also, Stack Overflow is really good at this type of question.
posted by nomad at 12:26 PM on June 1, 2009 [1 favorite]