Cancellazione (o Update) di record di una tabella in join con un'altra in MySql

DELETE T1 
FROM T1
        LEFT JOIN
    T2 ON T1.key = T2.key 
WHERE
    T2.key IS NULL

link : https://www.mysqltutorial.org/mysql-delete-join/

Una variante per l'update ha questa sintassi

UPDATE  T1 
        LEFT JOIN
    T2 ON T1.key = T2.key 

SET T1.campo=T2.campo

WHERE
    T2.key IS NULL

Questa variante permette di utilizzare la tabella T1 all'interno dell'eventuale subquery T2

esempio:

UPDATE T1 INNER JOIN

(SELECT CAMPO FROM T1 WHERE T1.CONDIZIONE ) T2 ON T1.KEY=T2.KEY

SET T1.CAMPO=T2.CAMPO

WHERE T1.CONDIZIONE

Thanks Elson Ferhati 

Allineamento fra valore Sequence e campo ID della tabella

Scenario :

• Campo identity autoinc gestito con Trigger e Sequence 
• Database Oracle versione inferiore alla 12 (perchè dalla 12 è gestito autonomamente come SqlServer)

Nei casi in cui in campo ID ( identificativo univoco ) venga inserito direttamente senza prelevare il campo dalla sequence o per qualche altro motivo ( nel mio caso ripristino delle tabelle e non delle sequence ), è necessario riportare il valore della sequence al massimo valore del campo autoinc +1.

La procedura sotto elencata fa questo lavoro.

DECLARE

  last_used  NUMBER;
  curr_seq   NUMBER;
BEGIN
  SELECT MAX(<CampoAutoInc>) INTO last_used FROM <Tabella>;
  LOOP
    SELECT <Sequence>.NEXTVAL INTO curr_seq FROM dual;
    IF curr_seq >= last_used THEN EXIT;
    END IF;
  END LOOP;
END;

Esempio

DECLARE

  last_used  NUMBER;
  curr_seq   NUMBER;
BEGIN
  SELECT MAX(IDN) INTO last_used FROM TESTAXML ;

  LOOP
    SELECT TESTAXML_SEQ.NEXTVAL INTO curr_seq FROM dual;
    IF curr_seq >= last_used THEN EXIT;
    END IF;
  END LOOP;
END;

fonte : https://stackoverflow.com/questions/6099108/best-way-to-reset-an-oracle-sequence-to-the-next-value-in-an-existing-column

Funzioni che restituiscono tabelle e join

 
Di seguito una funziona che accetta dei parametri e restituisce, in questo caso, un record di una tabella : 

CREATE FUNCTION dbo.VLISTDATA (@Codice CHAR(5), @Articolo CHAR(30), @Data CHAR(8) )
RETURNS TABLE
AS
RETURN
(
     SELECT * FROM TABRILIS
WHERE CODICE=@Codice
AND ARTICOLO=@Articolo
AND DATAINVALI = (
SELECT MAX(DATAINVALI) DATAINVALI FROM TABRILIS
WHERE CODICE=@Codice
AND ARTICOLO=@Articolo
AND DATAINVALI <= @Data
)
)

L'interessante è però come mettere in JOIN questa funzioni con altre tabelle.

Non è possibile utilizzare una normale JOIN ma è necessario utilizzare una CROSS APPLY come nell'esempio sotto: 

select M.ARTICOLO,M.EPRLORDO,PIPPO.PREZZO
from TESDOCUM T INNER JOIN MOVIMAG M ON T.NUMERO=M.NUMERO
INNER JOIN ANAMAGA A ON M.ARTICOLO=A.CODICE
INNER JOIN CLIFO ON T.CLI_FOR=CLIFO.CODICE
INNER JOIN CFVEN ON T.CLI_FOR=CFVEN.CODCF
Cross apply VLISTDATA(CFVEN.CODLIST,M.ARTICOLO,T.DATAPROTOC) PIPPO

Nell'esempio viene richiamata la funzione VLISTDATA per ogni riga del documento passandogli il codice listino del cliente, il codice articolo e la data del movimento.
La funzione restituisce tutti i campi della tabella TABRILIS con il record corrispondente alla data passata.

NB. L'esempio di cui sopra è solo accademia per Sigla ed è inutile in quanto tale dato si trova già nel campo EPRLISTINO di MOVIMAG

Testato sul Sql Server 2016

link : 
https://dba.stackexchange.com/questions/86143/how-to-join-a-table-with-a-table-valued-function
https://docs.microsoft.com/it-it/sql/t-sql/queries/from-transact-sql?view=sql-server-ver15

Funzioni con parametri in SQL Server che restituiscono tabelle

In Sql Server è possibile creare delle funzioni passandogli dei parametri a avere in ritorno una tabella.
Una specie di vista con parametri

La sintassi è
CREATE FUNCTION ( )
RETURN TABLE AS
RETURN
( 
SELECT * FROM
WHERE =@Parametro
)

Nel mio caso specifico avevo bisogno di una vista che passandogli il magazzino e gli estremi delle date mi restituisse le giacenze del magazzino e del periodo di date indicato



CREATE FUNCTION dbo.VDgaTest (@Magazzino CHAR(3),@DaData CHAR(8), @AData CHAR(8))
RETURNS TABLE
AS
RETURN
(
     select ARTICOLO,
     round(sum ( 
     case 
      when INVENTARIO =  '+'  then QUANTITA
      when INVENTARIO =  '-'  then QUANTITA*-1 
      when CARICO =  '+'  then QUANTITA    
      when CARICO =  '-'  then QUANTITA*-1
      when ACARICO =  '+'  then QUANTITA  
      when ACARICO =  '-'  then QUANTITA*-1
      when SCARICO =  '-'  then QUANTITA
      when SCARICO =  '+'  then QUANTITA*-1 
      when ASCARICO =  '-'  then QUANTITA 
      when ASCARICO =  '+'  then QUANTITA*-1
     end),4) GIACENZA
    from MOVIMAG
    where 1=1
    and DATA>= @DaData 
    and DATA<= @AData 
    and TIPO <>  '..' 
    AND MAGAZZINO=@Magazzino
    group by ARTICOLO
)

La funzione si richiama semplicemente con un :
SELECT * FROM vDgaTest('000','20150101','20150831')

Rinumerazione cespiti - script sql

Il seguente script rinumera tutti i cespiti presenti in archivio

DECLARE @Codice CHAR(5)  
DECLARE @Categoria CHAR(3)
DECLARE @cmd NVARCHAR(500)  
DECLARE @Conta INT
DECLARE @ContaA CHAR(5)

DECLARE Categoria CURSOR FOR  
  SELECT distinct CATEGORIA FROM CESPITI  
  ORDER BY CATEGORIA

OPEN Categoria 
FETCH NEXT FROM Categoria INTO @Categoria
WHILE @@FETCH_STATUS = 0 BEGIN  
    SET @Conta = 0

    SET @cmd = 'DECLARE Cespiti CURSOR FOR  ' + 
               '    SELECT CODICE FROM CESPITI  ' +  
               '    WHERE CATEGORIA=' + '''' + @Categoria + '''' +  
               '    ORDER BY CODICE';  
    EXEC (@cmd)  

    OPEN Cespiti  
    FETCH NEXT FROM Cespiti INTO @Codice  
    WHILE @@FETCH_STATUS = 0 BEGIN  
           SET @Conta = @Conta + 1           
           SET @ContaA = Right('00000'+RTrim(LTrim(CAST(@Conta as CHAR(5)))),5)

           SET @cmd = 'UPDATE CESPITI SET CODICE=' + '''' + @ContaA + ''''+  ',' + 
                      ' CODPADRE=' + '''' + @ContaA + '''' + 
                      ' WHERE CATEGORIA=' + '''' + @Categoria + '''' +  
                      ' AND CODICE=' + '''' + @Codice + ''''
           EXEC (@cmd)  

           SET @cmd = 'UPDATE MOVCE SET CODCESPITE=' + '''' + @ContaA + ''''+  ',' + 
                      ' CODPADRE=' + '''' + @ContaA + '''' + 
                      ' WHERE CATEGORIA=' + '''' + @Categoria + '''' +  
                      ' AND CODCESPITE=' + '''' + @Codice + ''''
           EXEC (@cmd) 

           FETCH NEXT FROM Cespiti INTO @Codice  
    END  
    CLOSE Cespiti  
    DEALLOCATE Cespiti 

    -- Allinea il numeratore
    SET @Conta = @Conta + 1
    SET @ContaA = Right('00000'+RTrim(LTrim(CAST(@Conta as CHAR(5)))),5)
    SET @cmd = 'UPDATE NUCESP SET NUMEROCESP=' + '''' + @ContaA + '''' + 
                  ' WHERE CATEGORIA=' + '''' + @Categoria + ''''; 
    EXEC (@cmd) 


    FETCH NEXT FROM Categoria INTO @Categoria
end
CLOSE Categoria
DEALLOCATE Categoria

SQL UPDATE su tabella prendendo dati da altre tabelle

Una interessante sintassi per l'UPDATE su SQL SERVER

Esempio che riporta su un campo di MOVIVA la partita iva del cliente corrispondente

UPDATE MOVIVA SET MOVIVA.CODEVENTO = 'C2011' + CLIFO.PAIV
FROM MOVIVA
INNER JOIN CLIFO ON CLIFO.CODICE = MOVIVA.SOTTOCONTO
WHERE MOVIVA.DATAREG>='20110101' 
AND MOVIVA.DATAREG<='20111231'
AND MOVIVA.C_F='C'
AND MOVIVA.TIPODOCIVA='FT'

Migliorare le prestazioni su MOVCO

Con il crescere dei movimenti alcune operazioni contabili diventano particolarmente lente. Di seguito sono elencati gli script per la generazione di due indici sulla tabella MOVCO che aumentano le prestazioni in determinate operazioni.

• CREATE INDEX USRMOVCO1 ON MOVCO (ATT_PASSIV,SOSPESO_SN,SCADENZ_SN,DATASCAD)Velocizza presentazione effetti
• CREATE INDEX USRMOVCO2 ON MOVCO (RIFENUMRO)Velocizza ricontabilizzazione documenti

Ricalcolo saldi contabili ( Tasalsot )

La seguente query ricrea TASALSOT ( che quindi deve essere svuotato prima ).
La query è per SqlServer
Per gli altri db basta spostituire il funzione ISNULL con VALUE,NVL,IFNULL a seconda del db

INSERT INTO TASALSOT

SELECT CODICE,PIACON.SOTTOCONTO,

       ISNULL(LIRE.DARE,0) DARE,

       ISNULL(LIRE.AVERE,0) AVERE,

       ISNULL(LIRE.DAREINIT,0) DAREINIT,

       ISNULL(LIRE.AVEREINIT,0) AVEREINIT,

       ISNULL(LIRE.EDARE,0) EDARE,

       ISNULL(LIRE.EAVERE,0) EAVERE,

       ISNULL(LIRE.EDAREINIT,0) EDAREINIT,

       ISNULL(LIRE.EAVEREINIT,0) EAVEREINIT,

       DATULTMOV,

       NUMULTMOV

 FROM PIACON INNER JOIN ESC ON 1=1

LEFT OUTER JOIN (SELECT ESECOMPET,SOTTOCONTO,

                        SUM(CASE WHEN SEGNO='D' THEN IMPORTO ELSE 0 END) DARE,

                        SUM(CASE WHEN SEGNO='A' THEN IMPORTO ELSE 0 END) AVERE,

                        SUM(CASE WHEN SEGNO='D' AND TIPOCAUSAL='A' THEN IMPORTO ELSE 0 END) DAREINIT,

                        SUM(CASE WHEN SEGNO='A' AND TIPOCAUSAL='A' THEN IMPORTO ELSE 0 END) AVEREINIT,

                        SUM(CASE WHEN SEGNO='D' THEN EIMPORTO ELSE 0 END) EDARE,

                        SUM(CASE WHEN SEGNO='A' THEN EIMPORTO ELSE 0 END) EAVERE,

                        SUM(CASE WHEN SEGNO='D' AND TIPOCAUSAL='A' THEN EIMPORTO ELSE 0 END) EDAREINIT,

                        SUM(CASE WHEN SEGNO='A' AND TIPOCAUSAL='A' THEN EIMPORTO ELSE 0 END) EAVEREINIT,

                        MAX(DATAREG) DATULTMOV,

                        MAX(NUMERO) NUMULTMOV

                 FROM MOVCO 

                 WHERE SOSPESO_SN='N'

                 AND TEMPORA_SN='N'

                 AND SIMULAZ_SN='N'

                 AND SOLOANA_SN<>'S'

                 GROUP BY ESECOMPET,SOTTOCONTO) LIRE ON ESC.CODICE=LIRE.ESECOMPET AND PIACON.SOTTOCONTO=LIRE.SOTTOCONTO                 

                 ORDER BY ESC.CODICE,PIACON.SOTTOCONTO

Diritti in Oracle

Per concedere un certo diritto su una tabella ad un altro user/schema la sintassi è la seguente :
GRANT <diritto> ON  <tabella> TO < user>
GRANT SELECT ON PIACON TO DITTA1

Esercizi su funzioni per date di SqlServer

Qui sotto c'è un esempio di due funzioni per la manipolazione di date su SQLServer


DATEADD aggiunge un valore ad una certa data
DATEDIFF restituisce la differenza fra due date
-------------------------------------------------------
select CODICE "Articolo", DESCRIZION "Descrizione", 
       substring(DATACREAZI,7,2)+'/'+substring(DATACREAZI,5,2)+'/'+substring(DATACREAZI,1,4) "Dt. Creazione", 
       DATEADD(year, 2, DATACREAZI) "Uscita produzione",
       substring(ULT_AGG,7,2)+'/'+substring(ULT_AGG,5,2)+'/'+substring(ULT_AGG,1,4) "Dt. Ult. Agg", 
       DATEDIFF(day, ULT_AGG, GETDATE()) "gg. dall'ult. aggiorn."
  FROM ANAMAGA
 WHERE STATO = 'S'
-------------------------------------------------------

"Uscita produzione":  2 anni successivi a DATACREAZI
"gg. dall'ult. aggiorn.":  differenza in giorni dal valore del campo ULT_AGG alla data odierna.

Grazie a Gianni per la ricerca

Riferimenti su web
  http://www.w3schools.com/sql/func_dateadd.asp
  http://www.sql-server-helper.com/tips/date-formats.aspx

Trigger Sql Server

E' possibile disabilitare e abilitare i trigger senza doverli cancellare e ricreare ( magari per aggiornamenti massivi ).
La sintassi è :

• DISABLE TRIGGER nometrigger_1[,nometrigger_2[,nometrigger_n]] ON nometabella
• ENABLE TRIGGER nometrigger_1[,nometrigger_2[,nometrigger_n]] ON nometabella

Query per contare i record nelle tabelle

Queste due query servono per estrarre un elenco delle tabelle e dei record in esse contenute.

1) QUERY SCRIPT PER GENERAZIONE QUERY DI CONTA:
x SQL Server;
select '( SELECT ''', sys.tables.name ,'''AS TABELLA, COUNT(*) AS TROVATI FROM ', sys.tables.name,') UNION '
from sys.tables
order by sys.tables.name

x Oracle;

select '( SELECT '''|| table_name ||''' AS TABELLA, COUNT(*) AS TROVATI FROM '|| table_name||') UNION '

from sys.all_tables

 WHERE OWNER='DITTA1'

order by table_name

NB. per Oracle è sufficiente interrogare la vista sys.table_name, anche se il campo NUM_ROWS non mi sembra aggiornato in tempo reale

SELECT table_name,num_rows 

FROM sys.all_tables

WHERE owner='DITTA1'

ORDER BY num_rows DESC

Lanciare la query sopra con l'estrazione in formato testo (CTRL+T x formato
testo, CTRL+E per eseguire dal Query Analizer )

2) QUERY GENERATA:

(SELECT 'AGENTI ' AS TABELLA, COUNT(*) AS TROVATI FROM AGENTI ) UNION
(SELECT 'AGGRART ' AS TABELLA, COUNT(*) AS TROVATI FROM AGGRART ) UNION
.
.
(SELECT 'VERINPS ' AS TABELLA, COUNT(*) AS TROVATI FROM VERINPS ) UNION
(SELECT 'VETTORI ' AS TABELLA, COUNT(*) AS TROVATI FROM VETTORI )

Togliere come sopra l'ultima UNION;

Copiare la seconda query così generata e lanciarla al posto della prima:
ogni volta che si lancia restituisce il recordcount di ogni tabella.

Thanks Gianni

Sintassi di UPDATE in Db2

In db2 ( versione provata 7.2) è possibile usare una sintassi del comando UPDATE simile a quella della INSERT e cioè :

UPDATE tabella SET (lista campi)=(valori) WHERE filtro

es.

UPDATE MOVIMAG SET (ESERCIZIO,DATA)=('2009','20091118') WHERE NUMERO='0123456'

A me non piace e uso il metodo standard, ma lo posto qui per conoscenza.

Apici Apici Apici

Se in un campo, a causa di un'importazione, c'è l'apice, qui di seguito c'è l'sql per cambiarlo :

UPDATE ANAMAGA SET DESCRIZION=REPLACE(DESCRIZION,'''','`')
from ANAMAGA
WHERE DESCRIZION LIKE '%''%'

solo per Sql Server

Sql Server e Linked Server

... è possibile collegare due server Sql Server e fare in modo che un server faccia le query sull'altro con i Linked Server o Server Collegati

I Server Collegati si creano in Enterprise Manager (SqlServer 2000), nella sezione Security.

E' sufficiente specificare il nome del server e l'utente con cui ci si collega.

Lo stesso utente sul server collegato deve avere un database collegato altrimenti per default vede solo master.

Il riferimento alla tabella remota avviene nella forma :

servercollegato.database.dbo.tabella

es

SERVERFIVE.PIPPO.dbo.ANAMAGA

e la query è

SELECT * FROM SERVERFIVE.PIPPO.dbo.ANAMAGA

La query in questione però è analizzata ed eseguita in locale con traffico generato dalla lettura dei cataloghi sul server remoto per analizzare la query.

Per far analizzare ed eseguire la query direttamente sul server remoto ed avere in cambio solamente in dati è preferibile usare la funzione OPENQUERY.

La sintassi è semplice :

SELECT * FROM OPENQUERY(servercollegato,query)

L'esempio di sopra sarà trasformato in :

SELECT * FROM OPENQUERY(SERVERFIVE,'SELECT * FROM PIPPO.dbo.ANAMAGA')

I Linked Server si basano sugli OleDb Provider, quindi più in generale è possibile collegare un Sql Server con qualsiasi altro tipo di DB.

NB. Nel caso di collegamento ad una istanza ( es. NomeServer\SQLEXPRESS ), il nome del server collegato dovrà essere racchiuso fra parentesi quadre.

SELECT * FROM [NomeServer\SQLEXPRESS].PIPPO.dbo.ANAMAGA

o

SELECT * FROM OPENQUERY([NomeServer\SQLEXPRESS],'SELECT * FROM PIPPO.dbo.ANAMAGA')

thanks Duccio

Per approfondimenti : qui e qui

Comando CAST

E' possibile convertire il tipo di risultato di un campo in un altro, ad esempio una stringa in un numero con il comando CAST.

esempio per il db2 e Sqlserver

SELECT CAST(NUMEPROTOC AS INTEGER) NUM_DOC FROM TESDOCUM

SELECT CAST(EIMPORTO AS CHAR) IMPORTO FROM MOVCO

Spostamento in avanti di 1 del campo NUMERO

UPDATE MOVIMAG SET NUMERO=RIGHT(RTRIM('0000000'+CAST(CAST(NUMERO AS INT) + 1 AS CHAR)),7) WHERE NUMERO='XXXXXXX' (Sql Server)

UPDATE MOVIMAG SET NUMERO=RIGHT(RTRIM('0000000'||CAST(CAST(NUMERO AS INT) + 1 AS CHAR(7))),7) WHERE NUMERO='XXXXXXX' (Db2)