当前位置： 文江博客话题详情

不能在 FROM 子句中指定更新的目标表

发布于 2025-01-20 15:09:38 字数 930 浏览 4 评论 0原文

我有一个简单的 mysql 表：

CREATE TABLE IF NOT EXISTS `pers` (
  `persID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(35) NOT NULL,
  `gehalt` int(11) NOT NULL,
  `chefID` int(11) DEFAULT NULL,
  PRIMARY KEY (`persID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `pers` (`persID`, `name`, `gehalt`, `chefID`) VALUES
(1, 'blb', 1000, 3),
(2, 'as', 1000, 3),
(3, 'chef', 1040, NULL);

我尝试运行以下更新，但只收到错误 1093：

UPDATE pers P 
SET P.gehalt = P.gehalt * 1.05 
WHERE (P.chefID IS NOT NULL 
OR gehalt < 
(SELECT (
    SELECT MAX(gehalt * 1.05) 
    FROM pers MA 
    WHERE MA.chefID = MA.chefID) 
    AS _pers
))

我搜索了该错误并从 mysql 以下页面找到 http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html，但事实并非如此帮不上忙 我。

我该怎么做才能纠正sql查询？

原文

I have a simple mysql table:

CREATE TABLE IF NOT EXISTS `pers` (
  `persID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(35) NOT NULL,
  `gehalt` int(11) NOT NULL,
  `chefID` int(11) DEFAULT NULL,
  PRIMARY KEY (`persID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `pers` (`persID`, `name`, `gehalt`, `chefID`) VALUES
(1, 'blb', 1000, 3),
(2, 'as', 1000, 3),
(3, 'chef', 1040, NULL);

I tried to run following update, but I get only the error 1093:

UPDATE pers P 
SET P.gehalt = P.gehalt * 1.05 
WHERE (P.chefID IS NOT NULL 
OR gehalt < 
(SELECT (
    SELECT MAX(gehalt * 1.05) 
    FROM pers MA 
    WHERE MA.chefID = MA.chefID) 
    AS _pers
))

I searched for the error and found from mysql following page http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html, but it doesn't help me.

What shall I do to correct the sql query?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（12）

清秋悲枫 2025-01-27 15:09:38

问题是，无论出于什么愚蠢的原因，MySQL 不允许您编写这样的查询：

UPDATE myTable
SET myTable.A =
(
    SELECT B
    FROM myTable
    INNER JOIN ...
)

也就是说，如果您正在执行 UPDATE/INSERT/< code>DELETE 在表上，您不能在内部查询中引用该表（但是您可以引用该外部表中的字段...）

解决办法是替换myTable的实例在带有 (SELECT * FROM myTable) 的子查询中，如下所示

UPDATE myTable
SET myTable.A =
(
    SELECT B
    FROM (SELECT * FROM myTable) AS something
    INNER JOIN ...
)

这显然会导致必要的字段被隐式复制到临时表中，因此这是允许的。

我找到了这个解决方案

您不想在现实生活中只在子查询中SELECT * FROM table；我只是想让例子简单一些。实际上，您应该只选择最内层查询中所需的列，并添加一个好的 WHERE 子句来限制结果。

The problem is that MySQL, for whatever inane reason, doesn't allow you to write queries like this:

UPDATE myTable
SET myTable.A =
(
    SELECT B
    FROM myTable
    INNER JOIN ...
)

That is, if you're doing an UPDATE/INSERT/DELETE on a table, you can't reference that table in an inner query (you can however reference a field from that outer table...)

The solution is to replace the instance of myTable in the sub-query with (SELECT * FROM myTable), like this

UPDATE myTable
SET myTable.A =
(
    SELECT B
    FROM (SELECT * FROM myTable) AS something
    INNER JOIN ...
)

This apparently causes the necessary fields to be implicitly copied into a temporary table, so it's allowed.

I found this solution here. A note from that article:

You don’t want to just SELECT * FROM table in the subquery in real life; I just wanted to keep the examples simple. In reality, you should only be selecting the columns you need in that innermost query, and adding a good WHERE clause to limit the results, too.

回复 原文
唔猫 2025-01-27 15:09:38

您可以通过三个步骤进行此操作：

CREATE TABLE test2 AS
SELECT PersId 
FROM pers p
WHERE (
  chefID IS NOT NULL 
  OR gehalt < (
    SELECT MAX (
      gehalt * 1.05
    )
    FROM pers MA
    WHERE MA.chefID = p.chefID
  )
)

...

UPDATE pers P
SET P.gehalt = P.gehalt * 1.05
WHERE PersId
IN (
  SELECT PersId
  FROM test2
)
DROP TABLE test2;

或

UPDATE Pers P, (
  SELECT PersId
  FROM pers p
  WHERE (
   chefID IS NOT NULL 
   OR gehalt < (
     SELECT MAX (
       gehalt * 1.05
     )
     FROM pers MA
     WHERE MA.chefID = p.chefID
   )
 )
) t
SET P.gehalt = P.gehalt * 1.05
WHERE p.PersId = t.PersId

You can make this in three steps:

CREATE TABLE test2 AS
SELECT PersId 
FROM pers p
WHERE (
  chefID IS NOT NULL 
  OR gehalt < (
    SELECT MAX (
      gehalt * 1.05
    )
    FROM pers MA
    WHERE MA.chefID = p.chefID
  )
)

...

UPDATE pers P
SET P.gehalt = P.gehalt * 1.05
WHERE PersId
IN (
  SELECT PersId
  FROM test2
)
DROP TABLE test2;

or

UPDATE Pers P, (
  SELECT PersId
  FROM pers p
  WHERE (
   chefID IS NOT NULL 
   OR gehalt < (
     SELECT MAX (
       gehalt * 1.05
     )
     FROM pers MA
     WHERE MA.chefID = p.chefID
   )
 )
) t
SET P.gehalt = P.gehalt * 1.05
WHERE p.PersId = t.PersId
回复 原文
满身野味 2025-01-27 15:09:38

子查询中制作一个临时表（tempp）

UPDATE pers P 
SET P.gehalt = P.gehalt * 1.05 
WHERE P.persID IN (
    SELECT tempP.tempId
    FROM (
        SELECT persID as tempId
        FROM pers P
        WHERE
            P.chefID IS NOT NULL OR gehalt < 
                (SELECT (
                    SELECT MAX(gehalt * 1.05) 
                    FROM pers MA 
                    WHERE MA.chefID = MA.chefID) 
                    AS _pers
                )
    ) AS tempP
)

从我介绍的

Make a temporary table (tempP) from a subquery

UPDATE pers P 
SET P.gehalt = P.gehalt * 1.05 
WHERE P.persID IN (
    SELECT tempP.tempId
    FROM (
        SELECT persID as tempId
        FROM pers P
        WHERE
            P.chefID IS NOT NULL OR gehalt < 
                (SELECT (
                    SELECT MAX(gehalt * 1.05) 
                    FROM pers MA 
                    WHERE MA.chefID = MA.chefID) 
                    AS _pers
                )
    ) AS tempP
)

I've introduced a separate name (alias) and give a new name to 'persID' column for temporary table

回复 原文
吝吻 2025-01-27 15:09:38

在MySQL中，您无法通过同一表更新一个表。

您可以将查询分为两个部分，也可以

 UPDATE TABLE_A AS A
 INNER JOIN TABLE_A AS B ON A.field1 = B.field1
 SET field2 = ?

In Mysql, you can not update one table by subquery the same table.

You can separate the query in two parts, or do

 UPDATE TABLE_A AS A
 INNER JOIN TABLE_A AS B ON A.field1 = B.field1
 SET field2 = ?
回复 原文
遥远的绿洲 2025-01-27 15:09:38

这很简单。 For example, instead of writing:

INSERT INTO x (id, parent_id, code) VALUES (
    NULL,
    (SELECT id FROM x WHERE code='AAA'),
    'BBB'
);

you should write

INSERT INTO x (id, parent_id, code)
VALUES (
    NULL,
    (SELECT t.id FROM (SELECT id, code FROM x) t WHERE t.code='AAA'),
    'BBB'
);

or similar.

It's quite simple. For example, instead of writing:

INSERT INTO x (id, parent_id, code) VALUES (
    NULL,
    (SELECT id FROM x WHERE code='AAA'),
    'BBB'
);

you should write

INSERT INTO x (id, parent_id, code)
VALUES (
    NULL,
    (SELECT t.id FROM (SELECT id, code FROM x) t WHERE t.code='AAA'),
    'BBB'
);

or similar.

回复 原文
乖乖兔^ω^ 2025-01-27 15:09:38

Blueraja发布的方法很慢，我将其修改为
我用来从表中删除重复项。万一它可以帮助任何大桌子的人
原始查询

DELETE FROM table WHERE id NOT IN (SELECT MIN(id) FROM table GROUP BY field 2)

这需要更多时间：

DELETE FROM table WHERE ID NOT IN(
  SELECT MIN(t.Id) FROM (SELECT Id, field2 FROM table) AS t GROUP BY field2)

更快的解决方案

DELETE FROM table WHERE ID NOT IN(
   SELECT t.Id FROM (SELECT MIN(Id) AS Id FROM table GROUP BY field2) AS t)

The Approach posted by BlueRaja is slow I modified it as
I was using to delete duplicates from the table. In case it helps anyone with large tables
Original Query

DELETE FROM table WHERE id NOT IN (SELECT MIN(id) FROM table GROUP BY field 2)

This is taking more time:

DELETE FROM table WHERE ID NOT IN(
  SELECT MIN(t.Id) FROM (SELECT Id, field2 FROM table) AS t GROUP BY field2)

Faster Solution

DELETE FROM table WHERE ID NOT IN(
   SELECT t.Id FROM (SELECT MIN(Id) AS Id FROM table GROUP BY field2) AS t)
回复 原文
蘑菇王子 2025-01-27 15:09:38

MySQL不允许从表中选择并同时在同一表中进行更新。但是总有一个解决方法：）

无效

UPDATE table1 SET col1 = (SELECT MAX(col1) from table1) WHERE col1 IS NULL;

这

UPDATE table1 SET col1 = (SELECT MAX(col1) FROM (SELECT * FROM table1) AS table1_new) WHERE col1 IS NULL;

MySQL doesn't allow selecting from a table and update in the same table at the same time. But there is always a workaround :)

This doesn't work >>>>

UPDATE table1 SET col1 = (SELECT MAX(col1) from table1) WHERE col1 IS NULL;

But this works >>>>

UPDATE table1 SET col1 = (SELECT MAX(col1) FROM (SELECT * FROM table1) AS table1_new) WHERE col1 IS NULL;
回复 原文
残花月 2025-01-27 15:09:38

作为参考，您还可以使用mySQL变量来保存临时结果，例如：

SET @v1 := (SELECT ... );
UPDATE ... SET ... WHERE x=@v1;

https://dev.mysql.com/doc/refman/5.7/en/user-variables.html

Just as reference, you can also use Mysql Variables to save temporary results, e.g.:

SET @v1 := (SELECT ... );
UPDATE ... SET ... WHERE x=@v1;

https://dev.mysql.com/doc/refman/5.7/en/user-variables.html

回复 原文
醉南桥 2025-01-27 15:09:38

Mariadb从10.3.x起将此提升（均用于delete和update）：

更新 - 具有相同源的语句

Mariadb 10.3.2，更新语句可能具有相同的源和目标。

在Mariadb 10.3.1之前，以下更新语句将无法使用：

 更新T1设置C1 = C1+1其中C2 =（从T1中选择Max（C2））;
  错误1093（HY000）：指定表“ T1”两次， 
  作为“更新”的目标，也是数据的单独来源

来自Mariadb 10.3.2，该语句成功执行：

 更新T1设置C1 = C1+1其中C2 =（从T1中选择Max（C2））;

delete-相同的源和目标表 < /p>

无法从具有相同源和目标的表中删除的表10.3.1。从Mariadb 10.3.1开始，这是可能的。例如：

 从t1中删除c1中的c1（从t1 b中选择b.c1，其中b.c2 = 0）;

<

a href =“ https://dbfiddle.uk/?rdbms = mariadb_10.2& fiddle = abec400809c158f3e27d27d24a70e540e54d4d4d4d /DBFIDDLE.UK/?rdbms=mariaDB_10.3＆amp; fiddle = abec400809c158f3f3e27d24a70e54d4d4d4d4d4d'r =“ noreferrer”

MariaDB has lifted this starting from 10.3.x (both for DELETE and UPDATE):

UPDATE - Statements With the Same Source and Target

From MariaDB 10.3.2, UPDATE statements may have the same source and target.

Until MariaDB 10.3.1, the following UPDATE statement would not work:

UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1);
  ERROR 1093 (HY000): Table 't1' is specified twice, 
  both as a target for 'UPDATE' and as a separate source for data

From MariaDB 10.3.2, the statement executes successfully:

UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1);

DELETE - Same Source and Target Table

Until MariaDB 10.3.1, deleting from a table with the same source and target was not possible. From MariaDB 10.3.1, this is now possible. For example:

DELETE FROM t1 WHERE c1 IN (SELECT b.c1 FROM t1 b WHERE b.c2=0);

DBFiddle MariaDB 10.2 - Error

DBFiddle MariaDB 10.3 - Success

回复 原文
依 靠 2025-01-27 15:09:38

如果您试图从tablea中读取fielda并将其保存在同一表上的fieldb上，则当fieldc = fieldd时，您可能需要考虑这一点。

UPDATE tableA,
    tableA AS tableA_1 
SET 
    tableA.fieldB= tableA_1.filedA
WHERE
    (((tableA.conditionFild) = 'condition')
        AND ((tableA.fieldc) = tableA_1.fieldd));

上面的代码在条件范围符合您的病情时，将从fielda到fieldb的值复制。这也适用于ADO（例如访问）

来源：尝试过自己

If you are trying to read fieldA from tableA and save it on fieldB on the same table, when fieldc = fieldd you might want consider this.

UPDATE tableA,
    tableA AS tableA_1 
SET 
    tableA.fieldB= tableA_1.filedA
WHERE
    (((tableA.conditionFild) = 'condition')
        AND ((tableA.fieldc) = tableA_1.fieldd));

Above code copies the value from fieldA to fieldB when condition-field met your condition. this also works in ADO (e.g access )

source: tried myself

回复 原文
各自安好 2025-01-27 15:09:38

其他解决方法包括在子查询中使用 SELECT DISTINCT 或 LIMIT，尽管这些对具体化的影响并不明确。这对我有用

如 MySql Doc 中所述

Other workarounds include using SELECT DISTINCT or LIMIT in the subquery, although these are not as explicit in their effect on materialization. this worked for me

as mentioned in MySql Doc

回复 原文
无敌元气妹 2025-01-27 15:09:38

您可以使用此查询

DELETE FROM test
WHERE (column_1, column_2, column_3) IN (
    SELECT column_1, column_2, column_3
    FROM test
    GROUP BY column_1, column_2, column_3
    HAVING COUNT(*) > 1
);

在此处输入图像描述

来

在此处输入图像描述

you can use this query

DELETE FROM test
WHERE (column_1, column_2, column_3) IN (
    SELECT column_1, column_2, column_3
    FROM test
    GROUP BY column_1, column_2, column_3
    HAVING COUNT(*) > 1
);

enter image description here

to

enter image description here

回复 原文
~没有更多了~

关于作者

花开雨落又逢春i

暂无简介

文章
评论
27 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

佚名

文章 0 评论 0

今天

文章 0 评论 0

゛时过境迁

文章 0 评论 0

达拉崩吧

文章 0 评论 0

呆萌少年

文章 0 评论 0

孤者何惧

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文