I'm sure the answer is a grim one, but suppose I have tables "foo_1" and "foo_2", each with unique primary keys. I then create a MERGE table named "foo". I have a routine which moves rows from one table to another thusly:
LOCK TABLES foo_1 WRITE, foo_2 WRITE; INSERT INTO foo_2 SELECT * FROM foo_1 WHERE id = 100; DELETE FROM foo_1 WHERE id = 100; UNLOCK TABLES;
Unfortunately, this seems to completely hose any selects on the MERGE table: all selects die with "read_const: Got error 127 when reading table" until I issue "FLUSH TABLES".
Is there a Right Way to move a row from "foo_1" to "foo_2" short of explicitly locking "foo"? It seems unwieldy to have to supply the names of any and all MERGE tables to my routine.
____________________________________________________________ Eamon Daly
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
