postgresql : Eseguire una SQL UPDATE query di test

In diverse circostanze risutla utile eseguire una query di UPDATE in modalità test senza che le modifiche vengano salvate e  diventino persistenti.

Per realizzare una query UPDATE di tipo test in postgresql può essere usato il seguente schema:

BEGIN;
UPDATE .........  ;
SELECT  .......   ;
ROLLBACK  ;

Di seguito un esempio pratico

BEGIN;
UPDATE esf SET 
 "IDUser" = esf.itemid WHERE 
 esf.email IN ( SELECT esf.email 
 -- SELECT contacts.itemid, contacts.confirmdate, contacts.email, esf.itemid AS e_itemid, esf.email AS e_email
 FROM contacts
 LEFT JOIN esf ON contacts.email::text = esf.email::text
 WHERE contacts.email::text = esf.email::text
 ORDER BY esf.createddate ) ;
SELECT"IDUser" FROM esf WHERE "IDUser" > 0 ;
ROLLBACK;

Esempio semplice

BEGIN ;
UPDATE esf SET "Organization" = company ;
SELECT "Organization" FROM esf WHERE "Organization" is NOT NULL ;
ROLLBACK ;

Altro esempio di sql query di test utilizzando query tools di pgadmin3 in postgresql  con le istruzioni di test roolback incluso disabilitare con il comment  —

Query tool pgadmin3

-- BEGIN; 

UPDATE esf SET 
   "itemid" = contacts.itemid 
   FROM contacts 
   WHERE esf.email = contacts.email AND
   esf.email IN 
   ( SELECT esf.email       
       FROM contacts 
       LEFT JOIN esf ON contacts.email::text = esf.email::text
       WHERE contacts.email::text = esf.email::text AND esf.id < 2000
       ORDER BY esf.createddate ) ;  

-- SELECT"itemid" FROM esf WHERE "itemid" < 22000 ; 

-- ROLLBACK;

sql UPDATE comando base

UPDATE contacts
SET firstname=name, lastname= name
WHERE firstname IS NULL AND name IS NOT NULL AND itemid < 100
 UPDATE sales
 SET payterms =
       CASE
          WHEN (SELECT SUM(qty) FROM sales s1
              WHERE sales.stor_id = s1.stor_id) < 10
             THEN 'On Billing'
         WHEN (SELECT SUM(qty) FROM sales s1
               WHERE sales.stor_id = s1.stor_id) < 100
              THEN 'Net 30'
        ELSE 'Net 60'
        END

INSERT INTO sitepage
SELECT sitepage_backup.*
FROM sitepage_backup
WHERE sitepage_backup."IDpage" = 442

Pagina riepilogativa

Query SQL  – PostgreSQL

Postgresql 

query di tipo plpgsql 

Creare eliminare database e tabelle

Annunci

postgresql : SQL UPDATE query

SQL UPDATE from another table con SELECT from

update  a table contacts  with value from another table users_ltd if they are matching on email  contacts.email= esf.email

1) version

UPDATE contacts
SET countrycode = users_ltd.countrycode
FROM users_ltd
WHERE contacts.email = users_ltd.email AND internal IS false;

update  a table contacts  with value from another table esf if they are matching on contacts contacts.itemid= esf.itemid

2) version

 UPDATE contacts
   SET countrycode =
   (SELECT SUBSTR( esf."Country" ,1,2)
      FROM esf
          WHERE  contacts.itemid = esf.itemid );

 

sql UPDATE con WHERE … IN ( SELECT ) e SELECT con INNER JOIN

UPDATE users_ltd
SET internal = false  

WHERE email IN (
select  users_ltd.email
FROM users_ltd
  INNER JOIN contacts
  ON users_ltd.email = contacts.email
  WHERE internal IS NULL
  ORDER BY contacts.email )

sql Update con due subqueries.

UPDATE  sitepage
   SET  numint = 1.1 * (SELECT   SUM(qty)
   FROM  sitepage_backup
   WHERE sitepage."IDpage" = sitepage_backup."IDpage" )
   WHERE "IDModuleCall" IN
     (SELECT "ModuleID"
      FROM   modules
      GROUP BY  "ModueleID"
      HAVING    sum("ModuleID") >=30)

Update completo di tutte le colonne della tabella

UPDATE sitepage AS S
   SET "IDMenuCatalog"=SB."IDMenuCatalog", "Page"=SB."Page", "PageKey"= SB."PageKey", "Menu"=SB."Menu", 
       "Pagetitle"= SB."Pagetitle", "Banner"=SB."Banner", "Pic3c"=SB."Pic3c", "Selezionato"=SB."Selezionato", "HTML"=SB."HTML", 
       "Description"=SB."Description", "PageBody"=SB."PageBody", "SolutionBody"=SB."SolutionBody", "Nota"=SB."Nota", "HtmlBody"=SB."HtmlBody", 
       "CreatedByUser"=SB."CreatedByUser", "CreatedDate"=SB."CreatedDate", "PageDescription"=SB."PageDescription", "PageKeywords"= SB."PageKeywords", 
       "ModuleIDCall"=SB."ModuleIDCall", "ModuleID"= SB."ModuleID"
 FROM sitepage_backup  AS SB
 WHERE S."IDpage" = 63;

sql eseguire una UPDATE query di TEST in Postgresql

schema:

BEGIN;
UPDATE .........  ;
SELECT  .......   ;
ROLLBACK  ;

Esempio pratico

BEGIN;
UPDATE esf SET 
 "IDUser" = esf.itemid WHERE 
 esf.email IN ( SELECT esf.email 
 -- SELECT contacts.itemid, contacts.confirmdate, contacts.email, esf.itemid AS e_itemid, esf.email AS e_email
 FROM contacts
 LEFT JOIN esf ON contacts.email::text = esf.email::text
 WHERE contacts.email::text = esf.email::text
 ORDER BY esf.createddate ) ;
SELECT"IDUser" FROM esf WHERE "IDUser" > 0 ;
ROLLBACK;

sql UPDATE comando base

UPDATE contacts
SET firstname=name, lastname= name
WHERE firstname IS NULL AND name IS NOT NULL AND itemid < 100
 UPDATE sales
 SET payterms =
       CASE
          WHEN (SELECT SUM(qty) FROM sales s1
              WHERE sales.stor_id = s1.stor_id) < 10
             THEN 'On Billing'
         WHEN (SELECT SUM(qty) FROM sales s1
               WHERE sales.stor_id = s1.stor_id) < 100
              THEN 'Net 30'
        ELSE 'Net 60'
        END

INSERT INTO sitepage
SELECT sitepage_backup.*
FROM sitepage_backup
WHERE sitepage_backup."IDpage" = 442

sql update usando la funzione replace
UPDATE links
 SET url= Replace (url, '/en/products/', '/it/prodotti/' )
 -- WHERE url LIKE '/en/products%';    // agiungere questa riga per avere in query tools le righe modificate

Risorse:

Postgresql 

Plpgsql 

Postgresql cancella record duplicati

Creare eliminare database e tabelle

..