Non è ansible specificare la tabella di destinazione per l’aggiornamento nella clausola FROM

Ho una semplice tabella 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); 

Ho provato a eseguire l’aggiornamento successivo, ma ricevo solo l’errore 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 )) 

Ho cercato l’errore e ho trovato da mysql seguente pagina http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html , ma non mi aiuta.

Cosa devo fare per correggere la query sql?

Il problema è che MySQL, per qualsiasi ragione insensata, non ti permette di scrivere query come questa:

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

Cioè, se stai facendo un UPDATE / INSERT / DELETE su una tabella, non puoi fare riferimento a quella tabella in una query interna ( puoi comunque fare riferimento a un campo da quella tabella esterna …)


La soluzione è sostituire l’istanza di myTable nella myTable con (SELECT * FROM myTable) , come questo

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

Ciò a quanto pare causa la copia implicita dei campi necessari in una tabella temporanea, quindi è consentita.

Ho trovato questa soluzione qui . Una nota di quell’articolo:

Non vuoi semplicemente selezionare SELECT * FROM table nella sottoquery nella vita reale; Volevo solo mantenere gli esempi semplici. In realtà, dovresti selezionare solo le colonne che ti servono in quella query più interna e aggiungere una buona clausola WHERE per limitare anche i risultati.

Puoi farlo in tre passaggi:

 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; 

o

 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 

Crea una tabella temporanea (tempP) da una sottoquery

 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 ) 

Ho introdotto un nome separato (alias) e do un nuovo nome alla colonna 'persID' per la tabella temporanea

In Mysql, non è ansible aggiornare una tabella tramite la sottoquery della stessa tabella.

È ansible separare la query in due parti o fare

  AGGIORNA TABELLA_A COME A
  TABELLA INTERNA INTERNA_A AS B ON A.field1 = B.field1
  SET field2 =? 

È abbastanza semplice Ad esempio, invece di scrivere:

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

dovresti scrivere

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

o simili.

L’approccio pubblicato da BlueRaja è lento, l’ho modificato mentre stavo usando per eliminare i duplicati dalla tabella. Nel caso in cui aiuti chiunque con tabelle grandi, Query originale

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

Questo richiede più tempo:

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

Soluzione più veloce

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

Se stai provando a leggere il campoA da tableA e salvalo sul campo B sulla stessa tabella, quando fieldc = fieldd potresti volerlo considerare.

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

Sopra il codice copia il valore dal campoA al campo B quando il campo condizione soddisfa la tua condizione. questo funziona anche in ADO (ad es. accesso)

fonte: ho provato me stesso

Come riferimento, puoi anche utilizzare Variabili Mysql per salvare risultati temporanei, ad esempio:

 SET @v1 := (SELECT ... ); UPDATE ... SET ... WHERE [email protected]; 

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

MariaDB lo ha rimosso a partire da 10.3.x (sia per DELETE che UPDATE ):

AGGIORNAMENTO – Dichiarazioni con la stessa fonte e destinazione

Da MariaDB 10.3.2, le istruzioni UPDATE possono avere la stessa fonte e destinazione.

Fino a MariaDB 10.3.1, la seguente istruzione UPDATE non funzionava:

 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 

Da MariaDB 10.3.2, l’istruzione viene eseguita correttamente:

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

DELETE: stessa tabella di origine e di destinazione

Fino a MariaDB 10.3.1, l’eliminazione da una tabella con la stessa fonte e destinazione non era ansible. Da MariaDB 10.3.1, questo è ora ansible. Per esempio:

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

DBFiddle MariaDB 10.2 – Errore

DBFiddle MariaDB 10.3 – Successo