none
SQL 2005 - 2000. Query di aggiornamento con funzioni di aggregazione RRS feed

  • Domanda

  • Ho 3 tabelle: TabellaA, TabellaB e TabellaC. Semplificando in base al problema, le tabelle sono così strutturate:

    TabellaA: IdOrdineProduzione|DataConsegnaOrdine

    TabellaB: IdOrdineProduzione|IdDoc|IdRiga

    TabellaC: IdDoc, IdRiga e DataConsegna

    In pratica la TabellaB fa da JOIN tra TabellaA e TabellaC

    Il mio obiettivo è aggiornare il campo DataConsegnaOrdine di TabellaA, con la minore tra le DataConsegna della TabellaC, ovviamente sfruttando i legami tra IdOrdineProduzione, IdDoc e IdRiga.

    Come posso fare?

    C'è da tenere conto inoltre che se tra le varie date ce n'è una a null, la data in TabellaA deve rimanere NULL. Usando la funzione MIN, una data non compilata viene presa come valore minore?

    Spero di aver spiegato bene il problema.

    Io ho scrit query, ma credo che mi possa andar bene anche una stored procedure. Deve girare sia su sql2000 che sql2005.

    Grazie a tutti. 


    • Modificato patapatty mercoledì 14 settembre 2011 10:51
    mercoledì 14 settembre 2011 10:49

Tutte le risposte

  • Il mio obiettivo è aggiornare il campo DataConsegnaOrdine di TabellaA, con la minore tra le DataConsegna della TabellaC, ovviamente sfruttando i legami tra IdOrdineProduzione, IdDoc e IdRiga.

    Come posso fare?

    Ciao,

    Prova a dare un'occhiata al seguente esempio:

    USE tempdb;
    
    CREATE TABLE dbo.A(
    IdOrdineProduzione int NOT NULL,
    DataConsegnaOrdine datetime NULL,
    CONSTRAINT PK_A PRIMARY KEY(IdOrdineProduzione)
    );
    
    CREATE TABLE dbo.C(
    IdDoc int NOT NULL,
    IdRiga int NOT NULL,
    DataConsegna datetime NULL,
    CONSTRAINT PK_C PRIMARY KEY(IdDoc, IdRiga)
    );
    
    CREATE TABLE dbo.B(
    IdOrdineProduzione int NOT NULL,
    IdDoc int NOT NULL,
    IdRiga int NOT NULL,
    CONSTRAINT PK_B PRIMARY KEY(IdOrdineProduzione, IdDoc, IdRiga),
    CONSTRAINT FK_BA FOREIGN KEY(IdOrdineProduzione)
    REFERENCES dbo.A(IdOrdineProduzione),
    CONSTRAINT FK_BC FOREIGN KEY(IdDoc, IdRiga)
    REFERENCES dbo.C(IdDoc, IdRiga)
    );
    
    INSERT dbo.A(IdOrdineProduzione)
    VALUES (1), (2), (3);
    
    INSERT dbo.C
    VALUES (10, 1, '2011-01-01'), (10, 2, '2011-02-02'), (10, 3, '2011-03-03')
         , (20, 1, '2011-04-04'), (20, 2, NULL)
         , (30, 1, '2011-05-05');
    
    INSERT dbo.B
    VALUES (1, 10, 1), (1, 10, 2), (1, 10, 3)
         , (2, 20, 1), (2, 20, 2)
         , (3, 30, 1);
    
    UPDATE A
    SET DataConsegnaOrdine = Q.DataConsegna
    FROM dbo.A
    JOIN (
        SELECT B.IdOrdineProduzione
             , MIN(COALESCE(C.DataConsegna, '1753-01-01')) AS DataConsegna
        FROM dbo.B
        JOIN dbo.C
        ON B.IdDoc = C.IdDoc
        AND B.IdRiga = C.IdRiga
        GROUP BY B.IdOrdineProduzione
    ) AS Q
    ON A.IdOrdineProduzione = Q.IdOrdineProduzione
    WHERE Q.DataConsegna <> '1753-01-01';
    
    SELECT *
    FROM dbo.A;
    
    /* Output:
    
    IdOrdineProduzione DataConsegnaOrdine
    ------------------ -----------------------
    1                  2011-01-01 00:00:00.000
    2                  NULL
    3                  2011-05-05 00:00:00.000
    
    (3 row(s) affected)
    
    */
    
    DROP TABLE dbo.B, dbo.A, dbo.C;
    
    

    In sostanza la tabella derivata calcola la DataConsegna minima per ogni IdOrdineProduzione e nel caso sia NULL assegna il valore minimo per il data type datetime. Il comando di UPDATE aggiorna la colonna DataConsegnaOrdine della tabella A prendendo in esame le righe corrispondenti la cui DataConsegna sia diversa dal valore minimo (ovvero NULL).

    Ciao!


    Lorenzo Benaglia
    Microsoft MVP - SQL Server
    http://blogs.dotnethell.it/lorenzo
    http://social.technet.microsoft.com/Forums/it-IT/sqlserverit
    mercoledì 14 settembre 2011 12:37
    Moderatore
  • Ciao,

    se non devi per forza usare funzioni di aggregazione, puoi usare una SELECT TOP 1 ... ordinata per DataConsegna.
    Guarda questo esempio in cui ho preso in prestito parte del codice postato da Lorenzo :-)

    USE tempdb;
    
    CREATE TABLE dbo.A(
    IdOrdineProduzione int NOT NULL,
    DataConsegnaOrdine datetime NULL,
    CONSTRAINT PK_A PRIMARY KEY(IdOrdineProduzione)
    );
    
    CREATE TABLE dbo.C(
    IdDoc int NOT NULL,
    IdRiga int NOT NULL,
    DataConsegna datetime NULL,
    CONSTRAINT PK_C PRIMARY KEY(IdDoc, IdRiga)
    );
    
    CREATE TABLE dbo.B(
    IdOrdineProduzione int NOT NULL,
    IdDoc int NOT NULL,
    IdRiga int NOT NULL,
    CONSTRAINT PK_B PRIMARY KEY(IdOrdineProduzione, IdDoc, IdRiga),
    CONSTRAINT FK_BA FOREIGN KEY(IdOrdineProduzione)
    REFERENCES dbo.A(IdOrdineProduzione),
    CONSTRAINT FK_BC FOREIGN KEY(IdDoc, IdRiga)
    REFERENCES dbo.C(IdDoc, IdRiga)
    );
    
    INSERT dbo.A(IdOrdineProduzione)
    VALUES (1);
    INSERT dbo.A(IdOrdineProduzione)
    VALUES (2);
    INSERT dbo.A(IdOrdineProduzione)
    VALUES (3);
    
    INSERT dbo.C
    VALUES (10, 1, '2011-01-01');
    INSERT dbo.C
    VALUES (10, 2, '2011-02-02');
    INSERT dbo.C
    VALUES (10, 3, '2011-03-03');
    INSERT dbo.C
    VALUES (20, 1, '2011-04-04');
    INSERT dbo.C
    VALUES (20, 2, NULL);
    INSERT dbo.C
    VALUES (30, 1, '2011-05-05');
    
    INSERT dbo.B
    VALUES (1, 10, 1);
    INSERT dbo.B
    VALUES (1, 10, 2);
    INSERT dbo.B
    VALUES (1, 10, 3);
    INSERT dbo.B
    VALUES (2, 20, 1);
    INSERT dbo.B
    VALUES (2, 20, 2);
    INSERT dbo.B
    VALUES (3, 30, 1);
    
    UPDATE A
    SET DataConsegnaOrdine = (SELECT TOP 1 C.DataConsegna
                              FROM dbo.B
                              JOIN dbo.C
                              ON B.IdDoc = C.IdDoc
                              AND B.IdRiga = C.IdRiga
                              WHERE B.IdOrdineProduzione = A.IdOrdineProduzione
                              ORDER BY C.DataConsegna)
    FROM dbo.A
    
    SELECT *
    FROM dbo.A;
    
    /* Output:
    
    IdOrdineProduzione DataConsegnaOrdine
    ------------------ -----------------------
    1                  2011-01-01 00:00:00.000
    2                  NULL
    3                  2011-05-05 00:00:00.000
    
    (Righe interessate: 3)
    
    */
    
    DROP TABLE dbo.B,dbo.C,dbo.A;
    
    

     

    Ciao
    Giorgio Rancati

    mercoledì 14 settembre 2011 14:35
    Moderatore
  • Grazie mille a entrambi per le risposte.

    Purtroppo il cliente non mi aveva spiegato bene la situazione e c'è un accorgimento in più.

    Solo nel caso in cui TUTTE le date siano NULL allora la data in TabellaA deve essere impostata con NULL. Se invece ci sono alcune date Null e altre impostate, allora devo prendere la minore di queste.

    Usando la soluzione di Giorgio, a questo punto non ottengo quello che mi serve, in quanto se ci fosse anche solo una data NULL, verrebbe presa per prima e quindi usata per l'aggiornamento. Ma credo che non funzioni nemmeno quella di Lorenzo.

    A proposito, la funzione COALESCE cosa fa in pratica? Se ho capito bene non sostituisce il valore NUll con quello indicato, ma lo considera come tale.

    Se mi potete aiutare anche se vi ho fatto perdere tempo non avendovi dato da subito tutte le specifiche corrette, mi fareste un grosso favore.

    Un'idea, anche se penso sia un po' grossolana, è quella di usare la funzione Min senza il COLAESCE (oppure SELECT TOP 1 ... ordinata per DataConsegna escludendo quelle a NULL) e poi in un secondo momento, aggiornare a NULL solo se TUTTE le date sono NULL. Ma come faccio a sapere se tutte le date legate a IdOrdineProduzione sono a Null?

    Grazie

     

    mercoledì 14 settembre 2011 17:19
  • Ciao,

    come hai già intuito, per il nuovo scenario basta escludere le date Null dalla subquery

    UPDATE A
    SET DataConsegnaOrdine = (SELECT TOP 1 C.DataConsegna
                              FROM dbo.B
                              JOIN dbo.C
                              ON B.IdDoc = C.IdDoc
                              AND B.IdRiga = C.IdRiga
                              WHERE B.IdOrdineProduzione =  A.IdOrdineProduzione
                              AND C.DataConsegna IS NOT NULL
                              ORDER BY C.DataConsegna)
    FROM dbo.A
    
    
    


    in questo modo se tutte le date di un IdOrdineProduzione
    sono null la subquery non restituisce record e il campo viene aggiornato a NULL, altrimenti viene aggiornato con la data più bassa.

    Per la funzione coalesce qui trovi tutte le info. COALESCE (Transact-SQL)


    Ciao
    Giorgio Rancati

    mercoledì 14 settembre 2011 18:07
    Moderatore
  • Solo nel caso in cui TUTTE le date siano NULL allora la data in TabellaA deve essere impostata con NULL. Se invece ci sono alcune date Null e altre impostate, allora devo prendere la minore di queste.

    Con una piccola modifica otterrai il risultato desiderato:

    USE tempdb;
    
    CREATE TABLE dbo.A(
    IdOrdineProduzione int NOT NULL,
    DataConsegnaOrdine datetime NULL,
    CONSTRAINT PK_A PRIMARY KEY(IdOrdineProduzione)
    );
    
    CREATE TABLE dbo.C(
    IdDoc int NOT NULL,
    IdRiga int NOT NULL,
    DataConsegna datetime NULL,
    CONSTRAINT PK_C PRIMARY KEY(IdDoc, IdRiga)
    );
    
    CREATE TABLE dbo.B(
    IdOrdineProduzione int NOT NULL,
    IdDoc int NOT NULL,
    IdRiga int NOT NULL,
    CONSTRAINT PK_B PRIMARY KEY(IdOrdineProduzione, IdDoc, IdRiga),
    CONSTRAINT FK_BA FOREIGN KEY(IdOrdineProduzione)
    REFERENCES dbo.A(IdOrdineProduzione),
    CONSTRAINT FK_BC FOREIGN KEY(IdDoc, IdRiga)
    REFERENCES dbo.C(IdDoc, IdRiga)
    );
    
    INSERT dbo.A(IdOrdineProduzione)
    VALUES (1), (2), (3);
    
    INSERT dbo.C
    VALUES (10, 1, '2011-01-01'), (10, 2, '2011-02-02'), (10, 3, '2011-03-03')
         , (20, 1, '2011-04-04'), (20, 2, NULL)
         , (30, 1, NULL), (30, 2, NULL);
    
    INSERT dbo.B
    VALUES (1, 10, 1), (1, 10, 2), (1, 10, 3)
         , (2, 20, 1), (2, 20, 2)
         , (3, 30, 1), (3, 30, 2);
    
    UPDATE A
    SET DataConsegnaOrdine = Q.DataConsegna
    FROM dbo.A
    JOIN (
        SELECT B.IdOrdineProduzione
             , MIN(C.DataConsegna) AS DataConsegna
        FROM dbo.B
        JOIN dbo.C
        ON B.IdDoc = C.IdDoc
        AND B.IdRiga = C.IdRiga
        WHERE C.DataConsegna IS NOT NULL
        GROUP BY B.IdOrdineProduzione
    ) AS Q
    ON A.IdOrdineProduzione = Q.IdOrdineProduzione;
    
    SELECT *
    FROM dbo.A;
    
    /* Output:
    
    IdOrdineProduzione DataConsegnaOrdine
    ------------------ -----------------------
    1                  2011-01-01 00:00:00.000
    2                  2011-04-04 00:00:00.000
    3                  NULL
    
    (3 row(s) affected)
    
    */
    
    DROP TABLE dbo.B, dbo.A, dbo.C;
    

    Come già indicato da Giorgione è sufficiente escludere le righe con DataConsegna valorizzata a NULL.

    Ciao!


    Lorenzo Benaglia
    Microsoft MVP - SQL Server
    http://blogs.dotnethell.it/lorenzo
    http://social.technet.microsoft.com/Forums/it-IT/sqlserverit
    • Contrassegnato come risposta patapatty giovedì 15 settembre 2011 07:24
    • Contrassegno come risposta annullato patapatty giovedì 15 settembre 2011 07:32
    mercoledì 14 settembre 2011 19:27
    Moderatore
  • Di nuovo grazie mille a entrambi.

    C'è ancora un problema:

    La soluzione di Giorgio aggiorna a NULL la data della TabellaA nel caso in cui ci siano solo date NULL, ma lo fa anche se NON ci sono link con le altre tabelle. Invece in questo caso quella data non dovrebbe essere modificata

    La soluzione di Lorenzo invece fa l'aggiornamento solo se trova legami, però se le date sono tutte a NULL, non aggiorna a NULL la data in TabellaA.

    Nessuna idea per riuscire a ottenere entrambe le cose?

    Come idea a me viene:

    - uso la soluzione di Lorenzo

    - poi faccio un'ulteriore aggiornamento x aggiornare a NULL la data in TabellaA solo se TUTTE le date sono NULL. Ma come faccio a sapere se tutte le date legate a IdOrdineProduzione sono a Null? Pensavo di verificare con una funzione di aggregazione se il numero di date per ogni IdOrdineProduzione coincide con il numero di date a NULL. E' fattibile?

    Grazie

    Patrizia


    • Modificato patapatty giovedì 15 settembre 2011 07:47
    giovedì 15 settembre 2011 07:24
  • Forse ho trovato la soluzione.

    Uso la soluzione di Giorgio aggiungendo alla fine

    WHERE A.IdOrdineProduzione IN (SELECT IdOrdineProduzione FROM TabellaB)

    Così mi sembra funzioni.

     

    Patrizia

    giovedì 15 settembre 2011 07:53