Tuesday, October 4, 2011

MySQL Lock tables and subqueries

Example error:
Table 'table' was not locked with LOCK TABLES

Example:
LOCK TABLES x write, y write;
select * from x; #works
select * from y; #works

But:
select *,
(select fieldname from y where fieldcond=x.field2) as yname
from x where field1='xxx'; #failed with
#y tables is not locked

Solution:
LOCK TABLES x write, y write, y as yalias write;
select *,
(select fieldname from y yalias where fieldcond=x.field2) as yname
from x where field1='xxx';
#WORKS!

No comments: