Mysql – Sub-query in update function

MySQL

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?

Best Answer

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 !!!

Related Question