Home > Server > postgresql : SQL UPDATE query

postgresql : SQL UPDATE query

SQL UPDATE from another table con SELECT from
1) versione

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

2) versione

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

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

..

Advertisements
Categorie:Server Tag:, , ,
  1. Non c'è ancora nessun commento.
  1. No trackbacks yet.

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

%d blogger hanno fatto clic su Mi Piace per questo: