Eliminare tutti i record tranne il più recente?

Ho due tabelle di DB in un uno-a-molti rapporti. I dati assomiglia a questo:

select * from student, application

Resultset:

+-----------+---------------+---------------------+
| StudentID | ApplicationID | ApplicationDateTime |
+-----------+---------------+---------------------+
| 1         | 20001         | 12 April 2011       |
| 1         | 20002         | 15 May 2011         |
| 2         | 20003         | 02 Feb 2011         |
| 2         | 20004         | 13 March 2011       |
| 2         | 20005         | 05 June 2011        |
+-----------+---------------+---------------------+

Voglio eliminare tutte le applicazioni, ad eccezione di quella più recente. In altre parole, ogni studente deve avere solo un applicazione ad esso collegati. Utilizzando l’esempio precedente, i dati dovrebbe essere simile a questo:

+-----------+---------------+---------------------+
| StudentID | ApplicationID | ApplicationDateTime |
+-----------+---------------+---------------------+
| 1         | 20002         | 15 May 2011         |
| 2         | 20005         | 05 June 2011        |
+-----------+---------------+---------------------+

Come posso fare per costruire il mio DELETE per filtrare i record corretti?

  • È necessario che questi studenti hanno più di una applicazione in qualsiasi momento? Se non, si potrebbe avere un uno-a-uno di relazione.
  • Dal momento che questo è tagged [plsql], suppongo che l’Oracolo.
  • Sì, è Oracle.
  • Per inciso, io non riesco a modificare lo schema del DB, o di modificare le relazioni tra tabelle etc.
  • Che cosa il vostro tabelle simile (ie: DESC student; DESC application)?
  • Istruzione select sembra sbagliato. Non unire le due tabelle.

InformationsquelleAutor sim | 2011-08-30



3 Replies
  1. 19
    DELETE FROM student
    WHERE ApplicationDateTime <> (SELECT max(ApplicationDateTime) 
                                  FROM student s2
                                  WHERE s2.StudentID  = student.StudentID)

    Data la lunga discussione nei commenti, si prega di notare le seguenti:

    La dichiarazione di cui sopra sarà lavorare su qualsiasi database che implementa correttamente istruzione di lettura a livello di consistenza indipendentemente da eventuali modifiche alla tabella durante l’esecuzione dell’istruzione.

    Database dove sicuramente so che questo funziona correttamente anche con simultanee modifiche alla tabella: Oracle (quello che questa domanda è circa), Postgres, SAP HANA, Firebird (e, probabilmente, MySQL utilizzando InnoDB). Perché tutti garantiscono una visione coerente dei dati al momento in cui l’istruzione. Cambiando il <> per < non cambierà nulla per loro (tra cui Oracle che questa domanda è circa)

    Per le suddette banche dati, la dichiarazione è non soggetto per il livello di isolamento perché letture fantasma o non ripetibili legge può avvenire solo tra più dichiarazioni – e non all’interno di un singolo istruzione.

    Per il database che non implementano MVCC correttamente e fare affidamento su di chiusura per gestire la concorrenza (e quindi il blocco simultaneo accesso in scrittura), questo potrebbe effettivamente resa risultati errati se la tabella viene aggiornata contemporaneamente. Per coloro che la soluzione utilizza < è probabilmente necessario.

    • Vi consiglio di usare ‘<‘ invece di ‘<>’ per evitare potenziali problemi con le transazioni concorrenti.
    • la dichiarazione di non vedere tutti i cambiamenti fatti da transazioni simultanee. Quindi cambiando <> per < non fare la differenza
    • comportamento a cui ti stai riferendo, forte dipende da un “livello di isolamento della transazione” (che è in gran parte-non impostare mai Serializable, in pratica, e sembra che anche “RR” non è sufficiente per rendere questo completamente sicuro come “il fantasma legge” possibile che in RR sembrano causare potenziali problemi di questa particolare istruzione); quindi – l’ ‘<‘ è più sicuro
    • no non è così, non con i moderni DBMS. Una singola istruzione vede una visualizzazione coerente di tutti i soggetti coinvolti tabelle come l’istruzione viene eseguita. Non potrà mai vedere qualsiasi concorrente modifiche. unico dichiarazione non può avere phantom legge o non ripetibili legge. L’unica cosa che questa dichiarazione non prendere cura di quando nuovi “ultima” vengono inserite le righe mentre viene eseguito, ma a quelli che potranno essere invisibile per l’esecuzione di istruzione cambiare <> per < non modificarla
    • Isolamento delle transazioni è una performance-vs-isolamento compromesso, e tutti sui blocchi (o MVCC-istantanea-di-essere-utilizzato); come conseguenza, si opera in termini di piani di esecuzione e di accedere a righe, e non in termini di istruzioni. Che è, se non stiamo parlando di MySQL+ISAM che non sono conformi le transazioni, lascia solo l’isolamento delle transazioni (e che mette i blocchi a livello di tabella per ogni istruzione di base, roba da pazzi; probabilmente, infatti, non importa per MySQL+ISAM, ma l’OP non menzionare MySQL+ISAM – ho ancora suggeriscono di cambiare a ‘<‘).
    • mi dispiace, questo è sbagliato. Citazione di Oracle manualeDatabase Oracle sempre applicata a livello di istruzione consistenza di lettura, che garantisce che i dati restituiti da una query è impegnata e coerente per un singolo punto nel tempo” e per l’impostazione predefinita del livello di isolamento read committed quel singolo punto nel tempo, è: “il momento in cui la dichiarazione è stata aperto
    • Ah, hai ragione su Oracle (e la stessa cosa POTREBBE stare in piedi per Postgres troppo – sono abbastanza vicino per quanto riguarda l’isolamento delle transazioni). Ancora – mentre è in grado di volare in questo modo per un MVCC a base di DB come Oracle o Postgres, è al 100% non è il caso per MSSQL e DB/2 (entrambi sono lock-base per impostazione predefinita, in modo che non possono permettersi questo tipo di comportamento; da MSSQL doc: “Read uncommitted. Possibile implementazione: le Transazioni non sono isolate le une dalle altre.”).
    • Inoltre – NON è richiesto dalla norma, sia (a livelli inferiori “Serializzabile” sono esplicitamente ammesso di avere “letture fantasma”; Oracle supera i requisiti delle norme – il che significa che c’è un motivo in più per usarlo, ma è ancora certamente non è il solo DB là fuori). Ergo, per un non meglio specificato DB, ‘<‘ è ancora una soluzione migliore.
    • In primo luogo: il DBMS non è “non specificato”. È Oracle. In secondo luogo: phantom legge, come definito nello standard SQL solo accadere quando una istruzione viene eseguita per secondo tempo. Mai nell’esecuzione di un unico istruzione.
    • DBMS NON è specificato nella domanda originale (tag e commenti sono molto deboli indicatori per evitare confusione). Così, si dovrebbe scrivere a grandi lettere: VALIDO SOLO PER ORACLE per evitare confusione. Come per lo standard – DB/2 e MSSQL sono presumibilmente compatibile (ehi, lo standard è stato originariamente scritto per corrispondenza DB/2 :-)), e le loro serrature non può impedire che il cattivo comportamento con la sua dichiarazione.
    • “E con SQL Server isolamento dello snapshot introdotto con il 2005 sarà, inoltre, si comportano allo stesso modo” – sì, è la volontà (e anche con Serializzabile in entrambi i DB MSSQL e/2); tuttavia, ci sono ancora altri risultati migliori livelli di là fuori che funzionano in modo diverso. Questo è il mio punto di tutto fin dall’inizio: perché utilizzare il costrutto che richiede un po ‘ di specifici livelli di isolamento quando c’è un costrutto (1 solo simbolo di meno) che non hanno questa limitazione?
    • Sì, ma perché NON usare <?

  2. 5

    È possibile utilizzare row_number() (o rank() o dense_rank(), o anche solo il rownum pseudocolonna) di applicare, per il record, e quindi utilizzare che per decidere quali scartare. In questo caso, l’ordinamento per applicationdatetime desc dà l’applicazione con la data più recente, per ogni studente, il grado di 1:

    select studentid, applicationid from (
        select studentid, applicationid,
            row_number() over (partition by studentid
                order by applicationdatetime desc) as rn
        from application
    )
    where rn = 1;
    
     STUDENTID APPLICATIONID
    ---------- -------------
             1         20002
             2         20005

    È possibile quindi eliminare tutto ciò con un grado maggiore di 1, che preseve i record che ti interessano:

    delete from application
    where (studentid, applicationid) in (
        select studentid, applicationid from (
            select studentid, applicationid,
                row_number() over (partition by studentid
                    order by applicationdatetime desc) as rn
            from application
        )
        where rn > 1
    );
    
    3 rows deleted.
  3. 1

    In un primo momento si può fare in modo

    DELETE FROM [student]
               or [application]
    WHERE (studentid, applicationid) NOT IN (SELECT StudentID
                                                   ,MAX(ApplicationID)
                                             FROM student
                                                 ,application
    group by StudentID);

    ma c’è un’altra soluzione, è possibile creare il backup tavolo, dopo di eliminare tutti i record nelle tabelle e dopo inserisci i tuoi dati (quello che volete) con max valori di selezionare nelle tabelle.

Lascia un commento