Sub-query in update function

Question

I have a really terrible set of data that I've inherited and part of my job is going through and cleaning it up to something that's actually usable. Currently, I'm trying to update a column in my table based on the name of entry.

Table: (category is currently null for almost everything)

+-----------------------------------------------------+
| programID | programName | programVersion | category |
+-----------------------------------------------------+

My query:

update Programs 
 set category="Adobe Suites" 
 where programName 
 in (
 select distinct programName 
 from Programs
 where programName regexp '^adobe.*cs[1-9]$'
 );

The problem is, no matter whatever I try I keep getting the error:

ERROR 1093 (HY000): You can't specify target table 'Programs' for update in FROM clause

I'm not sure what other way this could be done. Any ideas on how to replace the from statement with something else?

Answer 1

SUGGESTION #1

Why use a subquery at all? Try it like this:

update Programs 
set category="Adobe Suites" 
where programName regexp '^adobe.*cs[1-9]$';

PROS AND CONS : Simpler query with no subquery, but a single-pass full table scan.

SUGGESTION #2

Put the distinct names in a separate table and then do an UPDATE JOIN:

create table ProgramNames
 select distinct programName 
 from Programs
 where programName regexp '^adobe.*cs[1-9]$'
;
alter table ProgramNames add primary key (programName);
update Programs A INNER JOIN ProgramNames B USING (programName)
set A.category="Adobe Suites";
drop table ProgramNames;

PROS AND CONS : Clean UPDATE JOIN, but requires creating the temp table manually. This will be great if programName is indexed in the Programs table.

CAVEAT

There is great reason why you get that error message. It has to do with the MySQL Query Optimizer. Back on Feb 22, 2011, I wrote an answer to this post : Problem with MySQL subquery . I mentioned that there are times the MySQL Query Optimizer can make rows disappear intermittently in the optimization process. The join would not be clean internally. So, rather than rewrite it, MySQL simply kicks out an error so as not to have to rewrite. IMHO I think that maybe due to the way MySQL interacts with storage engines.

Give it a Try !!!

• Jacobm001: I'm trying really hard to justify my subquery...and I've got nothing. I think I adapted it from a previous query and just got stuck on doing it that way. I try not to be an idiot, I swear! :P
• RolandoMySQLDBA: Don't worry. MySQL only started doing subqueries in MySQL 5.0. Before MySQL 5.0, MySQL recommended rewriting subqueries as JOINs : dev.mysql.com/doc/refman/4.1/en/rewriting-subqueries.html. Perhaps a more mature RDBMS like Oracle or SQLServer would handle it much better since they do not accommodate more than one storage engine.

Answer 2

I really don't understand why you are using that pointless subquery, this can simply done by this basic query :

update Programs 
 set category='Adobe Suites' 
 where programName regexp '^adobe.*cs[1-9]$'

isn't that right?