esayr's blog 有友共尊田舍酒,无事面对古人书

[SQL语句]把所有的数据记录修改成Id = 1的记录　

群里朋友的问题．表名叫test,结构如下

就是把ID为2,3...的值,使用一个SQL语句,直接改为dsf

用 update test set a=(select b from test where id='1') where 1

提示错误:
SQL execution error # 1093 Response from the database:
You can't specify target table 'test' for upadte in FROM clause
早上问过fluke,这句可以实现:

UPDATE test,
(
SELECT b AS sub_data
FROM test
WHERE Id = 1
)q1
SET test.b = q1.sub_data WHERE 1

再更新: (by耿勇)
update test as aa,test as bb set bb.b=aa.b where aa.id=1