La domanda viene da uno scorato frequentatore di una mail list: Ho una tabella InnoDB con una chiave univoca sul campo numerico ID. Devo incrementare il campo di cinque unità, ma i miei tentativi non hanno dato frutto. Infatti, se provo a usare la formula SET ID = ID + 5, ottengo un errore di violazione di chiave. Questa è l’istruzione che ho tentato
BEGIN;
UPDATE nometabella SET ID = ID + 1;
COMMIT;
Credevo che InnoDB avrebbe contenuto l’errore per ogni singola violazione, e aggiornato tutta la tabella, e solo dopo avrebbe considerato che in realtà non c’è conflitto perché dopo che tutti i record sono stati aggiornati si ritorna a valori univoci. La mia tabella ha 12000 record. Non vorrei essere costretto a usare 12.000 istruzioni di UPDATE. Cosa posso fare?
Questa è stata la mia risposta:
InnoDB fa il suo mestiere, fermandosi al primo errore. Il meccanismo transazionale fa sì che la serie di istruzioni venga interrotta al primo errore, dando la possibilità al programmatore di chiamare un ROLLBACK. Quindi, al primo record, ci sarà un conflitto di valori che renderà impossibile l’aggiornamento.
Tuttavia, esiste una soluzione elegante, senza dover inviare 12.000 richieste. Basta chiedere l’aggiornamento con ordinamento decrescente:
UPDATE nometabella SET ID = ID + 1 ORDER BY ID DESC;
In questo modo, il motore aggiornerà la tabella in ordine decrescente di ID, garantendo che non ci saranno conflitti. Se invece si volesse decrementare il valore, basta usare l’ordinamento crescente (omettendo la clausola DESC).