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.