Let's say we want to calculate something, i.e. increase a counter

mysql_tip_on_inserting_and_subqueries.rb

# this doesn't work
"INSERT INTO table1 (field1) VALUE( (SELECT field1 FROM table1 LIMIT 1)+1 )"

# "You can't specify target table 'table1' for update in FROM clause"

# but this works
"INSERT INTO table1 (field1) VALUE( (SELECT field1 FROM (SELECT field1 FROM table1 LIMIT 1))+1 )"

# the FROM statement becomes just a repetition of the subquery.
# Thus, MySQL creates the temporary table with no problem.

# I understand this change isolates the INSERT's table1 from the SELECT's,
# so MySQL doesn't think it's the same, but isn't this funny, redundant and tricky?

This applies to UPDATE as well.