I'd like to update one field in a table so that all rows have the same value as the first row. I thought it would be simple:
UPDATE my_table SET my_field = (SELECT my_field FROM my_table ORDER BY id LIMIT 1);
But MySql doesn't like that.
ERROR 1093 (HY000): You can't specify target table 'my_table' for update in FROM clause
You can nest the subquery inside another:
UPDATE my_table
SET my_field = (SELECT my_field FROM (SELECT my_field FROM my_table ORDER BY id LIMIT 1) t);