At 5:14 PM -0500 9/18/03, <[EMAIL PROTECTED]> wrote:
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]



Reply via email to