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?