RE: LOCK TABLES and multi table UPDATE
On 22-Jan-2004 Michael McTernan wrote: Hi there, Thanks for your quick response! Why all the locks, temp tables and updates? You can just do: SELECT CASE WHEN B.y IS NULL THEN A.x ELSE B.y END AS newX FROM A LEFT JOIN B ON A.x = B.x Spot on - many thanks! I wasn't aware of the CASE function, but I've certainly learnt something now :) Also look at SELECT COALESCE(B.y, A.x) ... Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOCK TABLES and multi table UPDATE
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]
Re: LOCK TABLES and multi table UPDATE
Michael McTernan said: 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 | +---+ Why all the locks, temp tables and updates? You can just do: SELECT CASE WHEN B.y IS NULL THEN A.x ELSE B.y END AS newX FROM A LEFT JOIN B ON A.x = B.x Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOCK TABLES and multi table UPDATE
Hi there, Thanks for your quick response! Why all the locks, temp tables and updates? You can just do: SELECT CASE WHEN B.y IS NULL THEN A.x ELSE B.y END AS newX FROM A LEFT JOIN B ON A.x = B.x Spot on - many thanks! I wasn't aware of the CASE function, but I've certainly learnt something now :) Thanks, Mike -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: 22 January 2004 16:38 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: LOCK TABLES and multi table UPDATE Michael McTernan said: 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 | +---+ Why all the locks, temp tables and updates? You can just do: SELECT CASE WHEN B.y IS NULL THEN A.x ELSE B.y END AS newX FROM A LEFT JOIN B ON A.x = B.x Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
