使用 CONCAT 和 SELECT 更新 mysql 字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22378231/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update mysql field using CONCAT and SELECT
提问by Michel Ayres
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:
在消除了我的愚蠢错误INTO（我正在使用 INSERTS 并继续前进）之后，下面的错误显示出来了。还是行不通：
Affected rows: 0
[Err] 1093 - You can't specify target table 'tbl' for update in FROM clause
受影响的行：0
[Err] 1093 - 您不能在 FROM 子句中指定目标表 'tbl' 进行更新
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.
但是在替换下面的select之后。它带来了同样的错误。
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
[Err] 1064 - 你的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在“INTO tbl SET string_id = CONCAT(( SELECT tbl_alias.string_id ' at line 1
It's probably the CONCATtogether with SELECT. But I didn't find the solution...
它可能是CONCAT与SELECT. 但是我没有找到解决方案...
回答by Kickstart
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):-
请注意，在 MySQL 中，您不能使用 UPDATE 修改子查询中使用的表（尽管它周围有一些小问题）：-
https://dev.mysql.com/doc/refman/5.5/en/subqueries.html
https://dev.mysql.com/doc/refman/5.5/en/subqueries.html
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.
在 MySQL 中，您不能在子查询中修改表并从同一个表中进行选择。这适用于 DELETE、INSERT、REPLACE、UPDATE 和（因为可以在 SET 子句中使用子查询）LOAD DATA INFILE 等语句。
回答by Nailgun
Try to use UPDATEwithout INTO:
尝试使用UPDATE无INTO：
set @id = 3;
UPDATE 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;
Update:
更新：
Try this:
尝试这个：
set @id = 3;
UPDATE tbl
SET string_id =
CONCAT(SELECT string_id FROM (
SELECT tbl_alias.string_id
FROM tbl as tbl_alias
WHERE id = @id
) t1 ,',13,14,15,16') WHERE id = @id;
回答by Oleksii Kyslytsyn
This case helps sometimes to update ALLcolumn data for TESTINGpurpose:
这种情况有时有助于更新所有列数据以进行测试：
UPDATE customer_profile
SET businessId = CONCAT(businessId, ' new customer ', (id + 1))
