Обсуждение:

Поиск
Список
Период
Сортировка

От
"Matthew Sleight"
Дата:

Hi,

 

I’m trying to work out how to make an update query work with a join in posrgre.

 

I can write it in normal SQL, but postgre comes up with an error on the first ‘inner join’.  The online help (that is not very good) makes it seem like you do not use the ‘inner join’ at all, but just aggregate; but then postgre comes up with an error saying this won’t work either.

 

Any hints would be most helpful.

 

The code I started with is:

 

UPDATE public_consumption_hanson_uk_el_clear INNER JOIN public_consumption_hanson2 ON (public_consumption_hanson_uk_el_clear.supply_day = public_consumption_hanson2.supply_day) AND (public_consumption_hanson_uk_el_clear.supply_band_name = public_consumption_hanson2.supply_band_name) AND (public_consumption_hanson_uk_el_clear.meterreference = public_consumption_hanson2.meter_no) AND (public_consumption_hanson_uk_el_clear.meterpointreference = public_consumption_hanson2.mpan) SET public_consumption_hanson_uk_el_clear.actual_estimate = Min([consumption_hanson2].[actual_estimate]);

 

Thanks in advance!

 

Re:

От
Richard Broersma
Дата:
On Mon, Jul 12, 2010 at 4:31 AM, Matthew Sleight
<matthewsleight@encore-international.net> wrote:

> I can write it in normal SQL, but postgre comes up with an error on the
> first ‘inner join’.

Correct, inner joins in an update statement are violations of the SQL
standard and are not supported by PostgreSQL.  The following link
explains correct syntax that can be used with an update statement:

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

>  The online help (that is not very good)

If it isn't very good, do you have any suggestions on how it can be improved?


--SQL compliant syntax
UPDATE public_consumption_hanson_uk_el_clear AS A
   SET acutal_estimate
     = ( SELECT MIN( C.actual_estimate ) AS minimum_actual_estimate
           FROM public_consumption_hanson2 AS C
          WHERE C.supply_day = A.supply_day
            AND C.supply_brand_name = A.supply_brand_name
            AND C.meter_no = A.meterreference
            AND C.mpan = A.meterpiontreference ) AS B(
minimum_actual_estimate );

--Postgresql extension syntax
UPDATE public_consumption_hanson_uk_el_clear AS A
   SET acutal_estimate = B.minimum_actual_estimate
  FROM ( SELECT supply_day, supply_brand_name, meter_no, mpan,
                 MIN( C.actual_estimate ) AS minimum_actual_estimate
           FROM public_consumption_hanson2 AS C
       GROUP BY supply_day, supply_brand_name, meter_no ) AS B
 WHERE B.supply_day = A.supply_day
   AND B.supply_brand_name = A.supply_brand_name
   AND B.meter_no = A.meterreference
   AND B.mpan = A.meterpiontreference ;


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug