How to update all rows with value from first row

Question

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 could `SELECT` into a temporary table first to work around it. — tadman, Jul 02 '20 at 14:52

score 2 · Accepted Answer · answered Jul 02 '20 at 14:55

2

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);

answered Jul 02 '20 at 14:55

forpas

1 Answers1