mysql concat(select),使用CONCAT和SELECT更新mysql字段

EDIT

After removing my silly mistake of INTO (I was working with INSERTS and just keep going) the error below is showing. Still not working:

Affected rows: 0

[Err] 1093 - You can't specify target table 'tbl'

for update in FROM clause

I'm trying to create an update where I select all the previous data in the column, add a complementary string and save it as new data. The code is below (with the error)

Using only the select, the result:

set @id = 3;

SELECT tbl_alias.string_id

FROM tbl as tbl_alias

WHERE id = @id

-- the output `3,10,8,9,4,1,7,11,5,2,6,12`

I also tried with this query (the output is what I want)

SELECT CONCAT((

SELECT tbl_alias.string_id

FROM tbl as tbl_alias

WHERE id = @id

),',13,14,15,16') AS X

-- the output `3,10,8,9,4,1,7,11,5,2,6,12,13,14,15,16`

But after replacing the select below. It brings the same error.

The query

set @id = 3;

UPDATE INTO tbl

SET string_id =

CONCAT((

SELECT tbl_alias.string_id

FROM tbl as tbl_alias

WHERE id = @id

),',13,14,15,16') WHERE id = @id;

The error

[Err] 1064 - You have an error in your SQL syntax; check the manual

that corresponds to your MySQL server version for the right syntax to

use near ' INTO tbl SET string_id = CONCAT(( SELECT

tbl_alias.string_id ' at line 1

It's probably the CONCAT together with SELECT. But I didn't find the solution...

解决方案

Do you need the sub query?

UPDATE tbl

SET string_id = CONCAT(string_id, ',13,14,15,16')

WHERE id = @id;

Note that in MySQL you cannot modify using an UPDATE the table that is used in the sub query (although there are fiddles around it):-

In MySQL, you cannot modify a table and select from the same table in a subquery. This applies to statements such as DELETE, INSERT,

REPLACE, UPDATE, and (because subqueries can be used in the SET

clause) LOAD DATA INFILE.