Hi there, I've got a small issue which looks a little like a bug. I'm using MySQL 4.0.15-standard-log, on RedHat 9.0 with InnoDb tables.
Essentially I have two tables, one table is a list of integers, while the second is a table of integer pairs. e.g. +---+ +-----+-----+ | A | | B.x | B.y | +---+ +-----+-----+ | 1 | | 1 | 5 | | 2 | | 3 | 6 | | 3 | +-----+-----+ | 4 | +---+ What I want to do is form a temporary table containing A, but then with each value in A that matches B.x substituted for B.y. e.g. the desired result from the example would be a new table: +---+ | T | +---+ | 5 | | 2 | | 6 | | 4 | +---+ Here is what I try executing to get this, from my live database: LOCK TABLES labelfiles AS labelfile READ, branchfiles AS bfile READ; DROP TEMPORARY TABLE IF EXISTS tmpLabelFiles; -- This creates table 'A' from some other table CREATE TEMPORARY TABLE tmpLabelFiles ( PRIMARY KEY (id) ) SELECT labelfile.fileid AS id FROM labelfiles AS labelfile WHERE labelfile.labelid=18; -- This performs the substitution for 'B.x' -> 'B.y' UPDATE tmpLabelFiles AS tfile, branchfiles AS bfile SET tfile.id=bfile.replacementfileid WHERE tfile.id=bfile.branchfileid; UNLOCK TABLES; So far so good. Except that I get the following error when trying to execute the UPDATE: ERROR 1099: Table 'bfile' was locked with a READ lock and can't be updated If I lock 'bfile' with a WRITE lock it succeeds, but I'd prefer not to use a WRITE lock since other accesses to the table might be needed and this table can get quite large, and really, I'm not updating 'bfile' so should only need a READ lock, right? Has anyone else found this, and does anyone else know if there is an efficient work around? I've checked bugs.mysql.com and found nothing, is this a new bug? Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
