Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer
    
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 –  Jacobm001 Aug 9 '13 at 18:30
    
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. –  RolandoMySQLDBA Aug 9 '13 at 18:34

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?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.