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]