mysql - 临时表更新

Question

我正在 mysql 中创建一些临时表更新此表但出现错误

`CREATE TEMPORARY TABLE test (id varchar(50), name varchar(50));
INSERT INTO test (id, name) VALUES (1, 'LF/2019/0001');
SELECT name FROM test;
UPDATE test SET name = (SELECT name FROM test ORDER BY id DESC) + 1 WHERE id = 1;`

Error show : 

MySQL said: Documentation

#1137 - Can't reopen table: 'test'

请帮忙，谢谢，

Answer 1

如果你想更新那个特定的列一次，那么你可以使用下面的脚本

CREATE TEMPORARY TABLE test (id varchar(50), name varchar(50));
INSERT INTO test (id, name) VALUES (1, 'LF/2019/0001');
SELECT name FROM test;
UPDATE test SET name = concat('LF/2019/000',cast((cast(SELECT substring(name,9) FROM test ORDER BY id DESC) as unsigned integer) + 1) as char) WHERE id = '1';

drop table test