UPDATE avec alias non reconnu?!

Version imprimable

Salut a tous,

J'essaye de faire marcher un UPDATE utilisant un alias. Mais j'obtiens l'erreur suivante:

Citation:

ERROR: column "p" of relation "tbl_proposals" does not exist
LINE 2: SET P.fld_proposal_value = HP.fld_proposal_value
^

********** Error **********

ERROR: column "p" of relation "tbl_proposals" does not exist
SQL state: 42703
Character: 31

Voici la requete:
Code:

1 2 3 4 5 6 7 8 9 10 11 UPDATE tbl_proposals AS P SET P.fld_proposal_value = HP.fld_proposal_value FROM (SELECT fld_proposal_id, max(fld_id) AS max_id FROM tbl_proposals_history GROUP BY fld_proposal_id) AS mx, tbl_proposals_history AS HP WHERE HP.fld_id=mx.max_id AND P.fld_id=HP.fld_proposal_id AND (P.fld_proposal_value IS NULL OR P.fld_proposal_value = 0) AND (P.fld_status = 'Issued' OR P.fld_status ='On Hold' OR P.fld_status = 'Won' OR P.fld_status = 'Lost' OR P.fld_status = 'Dead') AND HP.fld_proposal_value IS NOT NULL
J'utilise PostgreSQL 8.3

05/10/2010, 13h14
ced

Bonjour,

Sous PostgreSQL, on ne peut pas mettre d'alias à la table qu'on souhaite mettre à jour. Les alias, en revanche, peuvent sur mettre sur les tables dans la clause FROM de l'update.

Il faut donc, dans la clause WHERE, que tu remplaces tous les "P" par "tbl_proposals".

ced

C'est ce que je m'etais dit, mais je suis alle voir la doc de PostgreSQL 8.3 et voila ce que j'y ai trouve:

Citation:

UPDATE [ ONLY ] table [ [ AS ] alias ]
SET { column = { expression | DEFAULT } |
( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
[ FROM fromlist ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ AS output_name ] [, ...] ]

Source

Bref, j'ai remplace mon alias et maintenant ca marche!

Requete finale:
Code:

1 2 3 4 5 6 7 8 9 10 11 UPDATE tbl_proposals SET fld_proposal_value = HP.fld_proposal_value FROM (SELECT fld_proposal_id, max(fld_id) AS max_id FROM tbl_proposals_history GROUP BY fld_proposal_id) AS mx, tbl_proposals_history AS HP WHERE HP.fld_id=mx.max_id AND tbl_proposals.fld_id=HP.fld_proposal_id AND (tbl_proposals.fld_proposal_value IS NULL OR tbl_proposals.fld_proposal_value = 0) AND (tbl_proposals.fld_status = 'Issued' OR tbl_proposals.fld_status ='On Hold' OR tbl_proposals.fld_status = 'Won' OR tbl_proposals.fld_status = 'Lost' OR tbl_proposals.fld_status = 'Dead') AND HP.fld_proposal_value IS NOT NULL
Merci :ccool:

05/10/2010, 15h30
estofilo

C'est dans la clause SET que l'alias n'est pas autorisé pour les colonnes de la table qui doit être mise à jour.
C.a.d qu'il faut faire SET colonne=... par opposition à SET alias.colonne=...
Pour le reste de la requête, l'alias peut être utilisé normalement.

En effet. Je viens de tester cela et ca marche!
Le nouvelle requette:

Code:

1
2
3
4
5
6
7
8
9
10
11
UPDATE tbl_proposals AS P
SET fld_proposal_value = HP.fld_proposal_value
FROM (SELECT fld_proposal_id, max(fld_id) AS max_id 
      FROM tbl_proposals_history 
      GROUP BY fld_proposal_id) AS mx, 
     tbl_proposals_history AS HP
WHERE HP.fld_id=mx.max_id
	AND P.fld_id=HP.fld_proposal_id 
	AND (P.fld_proposal_value IS NULL OR P.fld_proposal_value = 0)
	AND (P.fld_status = 'Issued' OR P.fld_status ='On Hold' OR P.fld_status = 'Won' OR P.fld_status = 'Lost' OR P.fld_status = 'Dead')
	AND HP.fld_proposal_value IS NOT NULL

Merci pour les explications :ccool: