Problema - "#1093 - You can't specify target table.."

[cadan2]

Salve
Ho una query di questo tipo:

"UPDATE time_prova SET time_prova.TIME = (SELECT UNIX_TIMESTAMP(CONCAT(SUBSTR(DATA, 7, 4),"-", SUBSTR(DATA, 4, 2),"-",SUBSTR(DATA, 1, 2)," 00:00:00")) FROM time_prova)"

mi da errore "#1093 - You can't specify target table 'time_prova' for update in FROM clause"
Ho letto un pò girando sul web che questo errore lo si ha quando si tenta di aggiornare una tabella prendendo dati dalla stessa.


La soluzione per quel che ho letto sarebbe creare un Alias , io ci ho provato ma non mi riesce,qualcuno sa aiutarmi?


La query è fatta bene perche se provo a copiare la tabella con nome diverso e modifico la query che da time_prova aggiorno time_prova1 funziona alla perfezione.

Ps:Ho creato una nuova discussione perchè ormai quella precendete aveva come titolo " Convertire data Sql" e per questa domanda era O.T.

[piero.mac]

manca l'indicazione del db usato. Comunque non puoi eseguire l'update selezionando il campo dalla tabella stessa.

Sarebbe interessante conoscere il formato della data su cui esegui quelle operazioni. Forse hai modi piu' semplici per eseguire l'operazione che ti prefiggi. Indica anche cosa vuoi ottenere da quella stringa sql.

[cadan2]

Allora, lavoro su un Db MySql (quello offerto da Aruba o in Locale con EasyPhp).
In pratica ho una tabella con oltre 400.000 record con delle date e altri campi.
Tali record sono le elaborazioni di dati di diversi programmi utilizzati nel tempo.

Quindi ogni programma non se mai posto il problema sul campo "DATA" e allora mi ritrovo a dover uniformare tipi di data come "20111230", "30/12/2011", "30-12-2011" e "30122011".
Il problema quindi è di dover unificare tutte le date in un tipo data unico che mi permetta poi di poter far operazioni di ricerca per data o per range di data.

La soluzione migliore era trovare un qualcosa di comodo sempre attraversi comandi Sql che mi permettesse magari con 4 query di trasformare tutte le date in formato Unix o in formato DATATIME.
Attraverso Php con una serie di query e poi dei while con relativi Update risulta una cosa complessa e soprattutto LUNGA!
Per ciò cercavo di capire se si potesse fare con un UPDATE un pò più complesso come quello indicato nel mio 1 post di questa discussione.

Spero d'esser stato più chiaro in questo intervento e di trovare la miglior soluzione.

[Talion]

devi fare:

UPDATE time_prova c
INNER JOIN time_prova c1 on c1.id = c.id
SET time_prova.TIME = (SELECT UNIX_TIMESTAMP(CONCAT(SUBSTR(c1.DATA, 7, 4),"-", SUBSTR(c1.DATA, 4, 2),"-",SUBSTR(c1.DATA, 1, 2)," 00:00:00")))

In sostanza fai una join su se stessa.

Io personalmente toglierei la conversione in timestamp e inserirei il valore in un campo datetime, ma è una cosa del tutto personale

[cadan2]

Ciao Talion
Grazie per la tua preziosa risposta.
Nel pomeriggio c'ho smanettato un pò e ho trovato soluzione grazie alla tua risposta,
con 9 update con innerjoin cambio tutte le mie date nel formato da te consigliato (DATATIME).
Ho notato che in DATATIME dopo posso estrarmi le date in molti formati sempre attraverso Sql e posso fare ricerche anche per RANGE di date cosa utilissima.

Unica nota al codice che mi hai scritto è quella di mettere c. al posto di time_prova. altrimenti mi da errore (lo dico giusto se legge qualcuno col mio stesso problema in futuro):

"UPDATE time_prova c
INNER JOIN time_prova c1 on c1.id = c.id
SET c.TIME = (SELECT UNIX_TIMESTAMP(CONCAT(SUBSTR(c1.DATA, 7, 4),"-", SUBSTR(c1.DATA, 4, 2),"-",SUBSTR(c1.DATA, 1, 2)," 00:00:00")));"

Grazie ancora

[cadan2]

Un ultimo quesito,
Ho modificato la mia tabella passando da campi VARCHAR 25 a DATATIME e di default per i campi vuoti mi viene assegnato il valore "0000-00-00 00:00:00" è normale?

Poi un domani quando voglio fare un estrapolazione exel con Php di alcuni dati per lasciare il campo vuoto nella cella dove effettivamente non c'è data devo sostituirlo io con Php la stringa "0000-00-00 00:00:00" con vuoto?

Stessa cosa se devo fare una query in cui cerco valore assente nel campo DATA1?
devo fare "Select * form tab where DATA1='0000-00-00 00:00:00'" rispetto a prima che facevo "Select * form tab where DATA1='' "???

E' giusto operare in questo modo per questi casi?

Non ho mai usato il campo DATATIME sin ora e quindi chiedo se anche voi operate cosi o c'è qualcosa di piu semplice che non conosco.
Grazie

[piero.mac]

un campo datetime ha come default quella serie di 0 che hai indicato.

se in un campo datetime assegni solo la data ora, min, sec assumono, per default, il valore di 00:00:00.
Questo puo' causare problemi di selezione in quanto dovrai anche indicare l'orario quando metterai per esempio where data = 'tua data'. Meglio se ti servono le sole date utilizzare due campi, uno date ed uno time in modo da facilitare la selezione dei record

[cadan2]

Piero per questo problema avevo pensato di usare il LIKE '2012-05-02%' perchè se poi divido le date in giorno e orario come faccio quando fanno ricerca per RANGE DI DATE?

[Talion]

Se vuoi mantenere il campo datetime nelle query per range devi fare qualcosa del tipo (vado a memoria con la sintassi ma dovrebbe all'incirca cosi)

SELECT * FROM TBL WHERE DATA >= '2012-01-01 00:00:00' AND DATA <= '2012-04-30 23:59:59'

cosi ti estrae dal 1 gennaio 2012 al 30 aprile 2012.

Per gli 0000-00-00 volendo puoi fare un update e mettere i valori a null se hai messo il campo nullabile

[cadan2]

Per il range ho fatto cosi:
SELECT * FROM TBL WHERE DATA BETWEEN '$data1' AND '$data2'
e sincermanente sembra funzionare.

Per quanto riguardo i campi a 0000-00-00 00:00:00 a questo punto faccio le query escludendo questa data quindi al posto di fare "WHERE DATA = '' "
farò WHERE DATA != '0000-00-00 00:00:00'
oppure WHERE NOT LIKE '0000-00-00 00:00:00'

Che ne pensate sono buone soluzioni? o c'è qualcosa di piu semplice e meno dispendioso a livelli di tempistica delle query?