Вы не можете указать целевую таблицу для обновления в предложении from

у меня есть простая таблица 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-запрос?

9 274
sql mysql mysql-error-1093

9 ответов:

проблема в том, что MySQL, по какой-то бессмысленной причине, не позволяет вам писать такие запросы:

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

то есть, если вы делаете UPDATE/INSERT/DELETE в таблице вы не можете ссылаться на эту таблицу во внутреннем запросе (вы можете однако ссылка на поле из этой внешней таблицы...)

решение заключается в замене экземпляра myTable в подзапросе с (SELECT * FROM myTable), как это

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

это, по-видимому, приводит к тому, что необходимые поля неявно копируются во временную таблицу, поэтому это разрешено.

Я нашел это решение здесь. Примечание из этой статьи:

Вы же не хотите просто SELECT * FROM table в подзапросе в реальной жизни, я просто хотел, чтобы держать простых примеров. На самом деле, вы должны только выбрать столбцы, которые вам нужны в этом внутреннем запросе, и добавить хороший WHERE предложения для ограничения и результаты тоже.

569

вы можете сделать это в три шага:

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
52

сделать временную таблицу (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
)

Я ввел отдельное имя (псевдоним) и дал новое имя столбцу "persID" для временной таблицы

20

в Mysql вы не можете обновить одну таблицу с помощью подзапроса той же таблицы.

вы можете разделить запрос на две части, или

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

Это довольно просто. Например, вместо написания:

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

вы должны написать

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

или аналогичные.

14

подход, опубликованный 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 x.Id from (SELECT MIN(Id) as Id from table GROUP BY field2) AS t)
12

Если вы пытаетесь прочитать 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 (например, access )

источник: пробовал сам

3

Так же, как ссылка, Вы можете также использовать переменные Mysql для сохранения временных результатов, например:

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

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

2

MariaDB поднял это, начиная с 10.3.х (как для DELETE и UPDATE):

UPDATE-операторы с тем же источником и целью

из MariaDB 10.3.2 инструкции UPDATE могут иметь один и тот же источник и цель.

до MariaDB 10.3.1, следующий оператор обновления не будет работать:

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

от MariaDB в 10.3.2, то оператор выполняется успешно:

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

удалить-тот же источник и целевая таблица

до MariaDB 10.3.1 удаление из таблицы с тем же источником и целью было невозможно. Из MariaDB 10.3.1 это теперь возможно. Например:

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

DBFiddle MariaDB 10.2-Ошибка

DBFiddle MariaDB 10.3-Успех

0