删除 - 我无法指定目标表？在使用查询进行删除时

29 浏览
0 Comments

删除 - 我无法指定目标表？在使用查询进行删除时

我有一个简单的mysql表：\n

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

\n我尝试运行以下更新语句，但只得到错误1093：\n

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

\n我搜索了这个错误并从mysql找到了以下页面http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html，但它对我没有帮助。\n我应该怎么做才能修正sql查询？

0

3 答案

0 Comments

问题出现的原因：在使用查询语句进行删除操作时，出现了“无法指定目标表”的错误。这是因为在查询中嵌套了多层SELECT语句，导致无法准确指定要删除的目标表。

解决方法：可以通过以下方式解决该问题：

1. 创建一个临时表（tempP）来存储子查询的结果。

2. 为临时表引入一个别名，并为'persID'列给予一个新的名称。

3. 在进行内部选择查询时，可以选择将值存储到变量中，而不是进行多层嵌套的SELECT查询。

具体的解决代码如下所示：

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
)

同时，需要注意以下代码中的错误：

SELECT (SELECT MAX(gehalt * 1.05).. - 第一个SELECT语句没有选择任何列。

0
0 Comments

问题的原因是在使用查询删除时无法指定目标表。解决方法是可以通过创建临时表来实现。下面是具体步骤：

1. 创建临时表test2，将符合条件的数据插入其中：

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

2. 使用更新语句将临时表中的数据更新到目标表pers中：

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

3. 删除临时表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

需要注意的是，使用临时表的方法可能会导致性能较慢，可以尝试为临时表创建索引或主键来提升性能。此外，还可以使用子查询或连接的方式重写查询语句。

0
0 Comments

问题的原因是MySQL不允许在内部查询中引用正在进行UPDATE/INSERT/DELETE操作的表。解决方法是将子查询中的myTable替换为(SELECT * FROM myTable)。这样做会隐式地将必要的字段复制到临时表中，从而允许操作。在实际应用中，应只选择需要的列，并添加一个良好的WHERE子句来限制结果。

问题的原因并不是无聊的。考虑语义。要么MySQL必须在更新开始之前保留表的副本，要么内部查询可能使用已经被查询更新的数据。这两种副作用都不一定是可取的，所以最安全的做法是强制您使用额外的表来指定将发生的操作。

其他数据库（如MSSQL或Oracle）没有这种任意限制。

从关系代数的角度来看，T和(SELECT * FROM T)是完全等价的。它们是同一个关系。因此，这是一个任意的、无聊的限制。更具体地说，这是一种将MySQL迫使做一些它显然可以做到的事情的变通方法，但由于某种原因在其较简单的形式中无法解析。

在MySQL 5.7.6及更高版本中，优化器可能会优化掉子查询，并仍然给出错误，除非您设置SET optimizer_switch = 'derived_merge=off';。

在某些情况下，由于表太大，接受的解决方案无法正常工作。查询永远不会完成，因为这占用了太多的内部资源。在这种情况下，可以创建一个具有内部查询的视图，并将其用于数据选择。

关于原子性的问题，这是一个单一查询，应该在任何事务性数据库中是原子的。但需要注意的是，MySQL使用的MyISAM引擎不支持事务。应该使用InnoDB引擎。

解决方案可能是创建一个视图。如果表很大，使用(SELECT * FROM table)与常规的连接和WHERE子句相比，这将返回整个表，然后再应用过滤器。这在构建大型应用程序时是一个巨大的问题。在MSSQL Server下，我不会遇到这个问题。但现在我使用的是MySQL，这让我非常生气。

感谢解释为什么需要表的副本。然而，MySQL中的查询性能非常差，执行速度非常慢。

MariaDB也没有这样的限制，MySQL真的很疯狂。

0