mise à jour des lignes de tableau dans postgresql à l'aide de sous-requêtes

À l'aide de postgresql 8.4, Mon objectif est de mettre à jour une table existante:

CREATE TABLE public.dummy
(
  address_id SERIAL,
  addr1 character(40),
  addr2 character(40),
  city character(25),
  state character(2),
  zip character(5),
  customer boolean,
  supplier boolean,
  partner boolean

)
WITH (
  OIDS=FALSE
);

Au départ, j'ai testé ma requête à l'aide d'instruction insert:

insert into address customer,supplier,partner
SELECT  
    case when cust.addr1 is not null then TRUE else FALSE end customer, 
    case when suppl.addr1 is not null then TRUE else FALSE end supplier,
    case when partn.addr1 is not null then TRUE else FALSE end partner
from (
    SELECT *
        from address) pa
    left outer join cust_original cust
        on (pa.addr1=cust.addr1 and pa.addr2=cust.addr2 and pa.city=cust.city 
            and pa.state=cust.state and substring(cust.zip,1,5) = pa.zip  )
    left outer join supp_original suppl 
        on (pa.addr1=suppl.addr1 and pa.addr2=suppl.addr2 and pa.city=suppl.city 
                and pa.state=suppl.state and pa.zip = substring(suppl.zip,1,5))
    left outer join partner_original partn
        on (pa.addr1=partn.addr1 and pa.addr2=partn.addr2 and pa.city=partn.city
                  and pa.state=partn.state and pa.zip = substring(partn.zip,1,5) )
where pa.address_id = address_id

étant Débutant je ne suis pas la conversion à l'instruction de mise à jour d'ie., mise à jour des lignes existantes avec les valeurs retournées par l'instruction select.
Toute aide est très appréciée.

  • avez-vous une sorte d'id dans la table d'adresses, qui peuvent être utilisés pour déterminer la ligne est existant?
  • oui je le fais mais sa sys généré.
InformationsquelleAutor stackover | 2011-06-06
  1. 608

    Postgres permet:

    UPDATE dummy
SET customer=subquery.customer,
    address=subquery.address,
    partn=subquery.partn
FROM (SELECT address_id, customer, address, partn
      FROM  /* big hairy SQL */ ...) AS subquery
WHERE dummy.address_id=subquery.address_id;

    Cette syntaxe n'est pas du SQL standard, mais il est beaucoup plus pratique pour ce type de requête de SQL standard. Je crois Oracle (au moins) accepte quelque chose de similaire.

    • il semble que je suis en train d'essayer un peu autre chose, par exemple. si il y a 3 bool colonnes c1,c2,c3 tous ensemble pour de faux, d'abord. mais, sur la base de la sous-requête est défini sur true. update set c1=TRUE where id in (subquery1),ensemble c2=TRUE where id in (subquery2), jeu c3=True where id in (subquery3). J'ai eu du succès quand je le fais partager ce que 3 mises à jour, mais je ne suis pas sûr de la façon d'atteindre le résultat avec une seule mise à jour. espérons que cela a du sens.
    • FWIW, Oracle n'accepter que le concept de base, cependant les performances de la mise à jour tendance à se dégrader sévèrement que les tables sont de plus en plus. C'est de l'o.k. même si, comme Oracle prend également en charge l'instruction MERGE.
    • Ce tout ne fonctionne pas dans postgresql 9.5, je reçois ERROR: 42P01: relation "dummy" does not exist
    • dummy doit être remplacé par le nom de la table que vous souhaitez mettre à jour. S'il vous plaît comprendre la question et la réponse avant d'essayer de l'appliquer.
    • Oui, désolé à ce sujet. Mon mauvais. N'a pas remarqué la question de départ a été l'aide de la table nommée "mannequin"
    • Il peut être intéressant de mentionner que, dans le début de la requête, il n'est pas nécessaire de spécifier le chemin d'accès à la colonne de gauche, qu'à la fin, sinon la db va se plaindre avec l'ERREUR: la colonne de référence "address_id" est ambigu
    • Il fonctionne sur Postgresql trop.

    InformationsquelleAutor Andrew Lazarus
  2. 104

    Vous êtes après la UPDATE FROM syntaxe.

    UPDATE 
  table T1  
SET 
  column1 = t2.column1 
FROM 
  table t2 
  INNER JOIN table t3 USING (column2) 
WHERE 
  t1.column2 = t2.column2;

    Références

    • Devrait être la réponse sélectionnée
    InformationsquelleAutor Brian Webster
  3. 42

    Si il n'y a pas de gains de performances à l'aide d'une jointure, alors je préfère les Expressions de Table Communes (expressions de table communes) pour des raisons de lisibilité:

    WITH subquery AS (
    SELECT address_id, customer, address, partn
    FROM  /* big hairy SQL */ ...
)
UPDATE dummy
SET customer = subquery.customer,
    address  = subquery.address,
    partn    = subquery.partn
FROM subquery
WHERE dummy.address_id = subquery.address_id;

    À mon humble avis un peu plus moderne.

    • La syntaxe n'est pas compatible avec les anciennes versions de Postgresql, avant de v9.1, (voir postgresql.org/docs/9.1/static/sql-update.html et les versions précédentes) je suis sur le v8.2, de sorte que vous avez à mettre l'ensemble de la CTE/instruction à l'intérieur de parenthèses après le mot-clé et il va fonctionner.
    • Solution la plus propre dans ce fil de discussion pour quelqu'un au-dessus de v9.1
    InformationsquelleAutor steevee
  4. 0
    update json_source_tabcol as d
set isnullable = a.is_Nullable
from information_schema.columns as a 
where a.table_name =d.table_name 
and a.table_schema = d.table_schema 
and a.column_name = d.column_name;
    InformationsquelleAutor Pugazendhi Asaimuthu