Nie można określić tabeli docelowej do aktualizacji w klauzuli FROM

Mam prostą tabelę 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);

Próbowałem uruchomić następującą aktualizację, ale pojawia się tylko błąd 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
))

Szukałem błędu i znalazłem na stronie mysql http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html , ale to mi nie pomaga.

Co mam zrobić, aby poprawić zapytanie SQL?

Problem polega na tym, że MySQL, z jakiegokolwiek bezsensownego powodu, nie pozwala ci pisać takich zapytań:

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

Oznacza to, że jeśli robisz UPDATE/ INSERT/ DELETEna stole, nie można odwołać się w tej tabeli zapytania wewnętrznej (ty może jednak odwołać się od tego zewnętrznego pola tabeli ...)

Rozwiązaniem jest zastąpienie wystąpienia myTablezapytania podrzędnego (SELECT * FROM myTable)takim, jak to

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

To najwyraźniej powoduje, że niezbędne pola są domyślnie kopiowane do tabeli tymczasowej, więc jest to dozwolone.

Znalazłem to rozwiązanie tutaj . Uwaga z tego artykułu:

W SELECT * FROM tableprawdziwym życiu nie chcesz tylko w podzapytaniu; Chciałem tylko, aby przykłady były proste. W rzeczywistości powinieneś wybierać tylko kolumny, których potrzebujesz w tym najbardziej wewnętrznym zapytaniu, i dodawać dobrą WHEREklauzulę, aby ograniczyć również wyniki.

Możesz to zrobić w trzech krokach:

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;

lub

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

W Mysql nie można aktualizować jednej tabeli, subquery tej samej tabeli.

Możesz podzielić zapytanie na dwie części lub zrobić

 AKTUALIZACJA TABELI_A JAKO A
 WEWNĘTRZNA DOŁĄCZ TABELA_A AS B NA A.field1 = B.field1
 SET pole 2 =? 

Utwórz tabelę tymczasową (tempP) z podzapytania

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
)

Wprowadziłem osobną nazwę (alias) i nadałem nową nazwę kolumnie „persID” dla tabeli tymczasowej

To całkiem proste. Na przykład zamiast pisać:

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

powinieneś pisać

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

lub podobne.

Podejście opublikowane przez BlueRaja jest powolne. Zmodyfikowałem je, ponieważ używałem do usuwania duplikatów z tabeli. W przypadku, gdy pomaga każdemu z dużymi tabelami Original Query

delete from table where id not in (select min(id) from table group by field 2)

Zajmuje to więcej czasu:

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

Szybsze rozwiązanie

DELETE FROM table where ID NOT IN(
   SELECT x.Id from (SELECT MIN(Id) as Id from table GROUP BY field2) AS t)

Jako odniesienie możesz również użyć Zmiennych MySQL, aby zapisać wyniki tymczasowe, np .:

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

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

Jeśli próbujesz odczytać pole A z tabeli A i zapisać je w polu B w tej samej tabeli, gdy fieldc = fieldd możesz to rozważyć.

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

Powyższy kod kopiuje wartość z pola A do pola B, gdy pole-warunek spełnia warunek. działa to również w ADO (np. dostęp)

źródło: próbowałem siebie

MariaDB zniosła to, zaczynając od 10.3.x (zarówno dla, jak DELETEi UPDATE):

AKTUALIZACJA - wyciągi z tym samym źródłem i celem

Od MariaDB 10.3.2 instrukcje UPDATE mogą mieć to samo źródło i cel.

Do MariaDB 10.3.1 następująca instrukcja UPDATE nie działała:

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

Od MariaDB 10.3.2 instrukcja wykonuje się pomyślnie:

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

DELETE - ta sama tabela źródłowa i docelowa

Do czasu wydania MariaDB 10.3.1 usunięcie tabeli z tym samym źródłem i celem nie było możliwe. Od MariaDB 10.3.1 jest to teraz możliwe. Na przykład:

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

DBFiddle MariaDB 10.2 - Błąd

DBFiddle MariaDB 10.3 - Sukces

Inne obejścia obejmują użycie SELECT DISTINCT lub LIMIT w podzapytaniu, chociaż nie mają one tak jednoznacznego wpływu na materializację. to zadziałało dla mnie

jak wspomniano w MySql Doc