Mise à jour d'une colonne avec les résultats d'une requête dans PostgreSQL

J'ai le tableau suivant dans PostgreSQL 9.2, qui contient des timbres:

gid [PK] (bigserial), timestamp_mes (timestamp sans fuseau horaire), time_diff (intervalle)
1, 2012-01-23 11:03:40, vide
2, 2012-01-23 11:03:42, vide
3, 2012-01-23 11:03:44, vide

J'ai ajouté un intervalle de colonne (time_diff) et tiens à le remplir avec le temps les valeurs de la différence résultant de cette requête:

SELECT timestamp_mes - lag(timestamp_mes, 1) 
over (order by timestamp_mes) as diff
from gc_entretien.trace order by timestamp_mes

J'ai essayé la requête suivante pour mettre à jour le time_diff colonne, sans succès:

UPDATE gc_entretien.trace set time_diff = 
(SELECT trace.timestamp_mes - lag(trace.timestamp_mes, 1) 
over (order by trace.timestamp_mes) 
from gc_entretien.trace order by timestamp_mes);

Cela entraîne une erreur:

ERREUR: plus d'une ligne renvoyée par une sous-requête en tant qu'expression

Comment dois-je procéder pour mettre à jour le time_diff colonne avec les valeurs résultant de la différence de temps de requête?

Pas sûr au sujet de la logique de votre application, mais la sélection retourne peut-être plus les lignes qui provoque une erreur lors de l'affectation dans une seule colonne (comme vous le faites dans votre UPDATE)... si le résultat est en première ligne renvoyée dans votre SELECT, utilisez LIMIT 1 à procéder à la cession possible. De toute façon le select ne semble pas être correctement conçus.
Il y a un problème avec ça; parce que la sous-requête est (pour l'instant) pas corrélés, LIMIT 1 va juste retourner la rangée du haut, de la période, pas un seul qui n'a rien à voir avec la ligne actuelle.
à droite, c'est pourquoi je dis qu'il n'est pas correctement conçu requête. D'abord @jatobat besoin de créer une requête qui renvoie exactement une valeur (celle qu'il a besoin en fonction de la logique d'application) puis la mettre comme sous-requête dans UPDATE requête... d'habitude le scénario est d'utiliser certaines informations sur les valeurs de la table (resp. ligne) cours de mise à jour dans la sous-requête en tant que condition(s) dans la sous-requête WHERE clause. Mais franchement dit: je ne comprends pas la logique de l'application et même ne veux pas comprendre 🙂

OriginalL'auteur jatobat | 2012-12-07

  1. 28

    Quelque chose comme ceci:

    with new_values as (
   SELECT gid, 
          timestamp_mes - lag(timestamp_mes, 1) over (order by timestamp_mes) as diff
   from gc_entretien.trace 
)
update gc_entretien.trace as tr
  set time_diff = nv.diff
from new_values nv
where nv.gid = tr.gid;
    +1. Cela ressemble droit à moi, et à me battre en 4 minutes. Ne savait jamais sur l'existence de WITH... AS... jusqu'à maintenant. Cool. 🙂
    que l'on appelle une "expression de table commune", et depuis 9.1 cela peut aussi être utilisé pour les instructions DML (avant qu'il ne puisse être utilisé pour la sélection normale états - y compris les requêtes récursives)
    Si vous le faites dans le T-SQL, alors il semble que vous ne pouvez pas l'alias de la table de mise à jour. Si vous utilisez le nom de la table explicitement (au lieu de tr dans ce cas), il serait un travail même si.
    eh bien, la question est balisé postgresql il est donc évident que cette réponse ne s'appliquent pas à SQL Server.
    Votre réponse est très utile pour SQL en général, c'était exactement ce que je cherchais. J'ai juste pensé que d'autres peuvent éprouver les mêmes je n'ai.

    OriginalL'auteur a_horse_with_no_name

  2. 3

    Vous ne pouvez pas utiliser directement une fonction de fenêtre dans une mise à JOUR, de sorte que vous besoin de l'utiliser dans un sous-SELECT - qui vous l'ont fait. Cependant, la façon dont vous avez essayé d'utiliser ce sous-SÉLECTIONNEZ dans votre mise à JOUR n'est pas valide la syntaxe. Vous avez besoin de mettre les sous-SÉLECTIONNEZ dans la clause from de votre mise à jour, comme l'a expliqué le Postgres docs ici:

    http://www.postgresql.org/docs/9.2/static/sql-update.html

    La syntaxe correcte pour ce que vous voulez faire est:

    UPDATE gc_entretien.trace t
SET time_diff = subquery.diff
FROM (SELECT {{SomeUniqueId}}, 
             timestamp_mes - lag(timestamp_mes, 1) over (order by timestamp_mes) as diff
      FROM gc_entretien.trace order by timestamp_mes) AS subquery
WHERE t.{{SomeUniqueId}} = subquery.{{SomeUniqueId}}

    Évidemment, vous aurez besoin de remplacer dans la colonne nom d'une id unique que vos lignes où j'ai écrit {{SomeUniqueId}}

    OriginalL'auteur Mark Amery

  3. 1

    En fait, vous obtenez cette erreur parce que votre sous-requête renvoie plusieurs résultats,

    Je ne suis pas en mesure de comprendre votre requête afin,

    Je vais vous donner un exemple pour le résoudre, 

    update table t1 set time_diff= select *your_operation* from table t2 where t1.id=t2.id

    Ici :-your_operation signifie la logique de la constatation de la différence de temps,

    Oui, c'est la forme générale de ce qu'il doit faire... peut-être remplir le reste de la requête?
    Cette réponse a fonctionné pour moi d'avoir un problème avec PostgreSQL 9.0.1. Merci.

    OriginalL'auteur Hunter