使用CONCAT和SELECT更新mysql字段 [英] Update mysql field using CONCAT and SELECT

问题描述

编辑

消除了我愚蠢的错误INTO(我正在使用INSERTS并继续前进)后，显示以下错误.仍然无法正常工作:

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

受影响的行:0

Affected rows: 0

[Err] 1093-您无法指定目标表'tbl' 在FROM子句中进行更新

[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)

仅使用选择结果:

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.

查询

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;

错误

[Err] 1064-您的SQL语法有错误；查看手册 对应于您的MySQL服务器版本以获取正确的语法 在'INTO tbl SET string_id = CONCAT((SELECT tbl_alias.string_id'在第1行

[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

---

可能是CONCAT和SELECT一起.但是我没有找到解决方案...

---

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

推荐答案

是否需要子查询?

UPDATE tbl 
SET string_id = CONCAT(string_id, ',13,14,15,16')
WHERE id = @id;

请注意，在MySQL中，您无法使用UPDATE修改子查询中使用的表(尽管周围有小玩意儿):-

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):-

https://dev.mysql.com/doc/refman/5.5/en/subqueries .html

在MySQL中，您不能修改表并在子查询中从同一表中选择.这适用于诸如DELETE，INSERT， REPLACE，UPDATE和(因为可以在SET中使用子查询 子句)LOAD DATA INFILE.

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.

这篇关于使用CONCAT和SELECT更新mysql字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！