I'm not quite sure why I haven't run across this in the past, but now that I have I am stumped. I am needing to update a table based on criteria found in it and one other table, but I am uncertain how to proceed. If I had subselects I would run the query as follows, I believe:
UPDATE suppliercatlink SET suppliercatlink.catid=124 WHERE suppliercatlink.supid IN (SELECT supplier.id FROM supplier WHERE supplier.company_name LIKE %exteri%) AND suppliercatlink.catid=10 ;
Knowing that this is not an option I figure maybe I could join the tables in my UPDATE statement like:
UPDATE suppliercatlink, supplier SET suppliercatlink.catid=124 WHERE supplier.company_name LIKE '%brick%' AND supplier.id=suppliercatlink.supid AND suppliercatlink.catid=10 ;
Looking at the documentation it appears this will not work, at least not with 3.23 which I am currently running. It appears that something of this nature would work if I upgraded to 4.0.4, but I really prefer to update mySQL before or after a project, not right in the middle of it. Can anyone help me figure out a way around this problem?
If you don't want to update to MySQL 4 (which will indeed allow you to run your second UPDATE above, then you'll need to code the equivalent logic in an application. Select the ID list from supplier for those records that need updating, then use them to construct a set of UPDATE statements for the suppliercatlink table.
Jay Drake [EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
