FAQ
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!

Search Discussions

  • Richard Broersma at Jul 12, 2010 at 4:25 pm

    On Mon, Jul 12, 2010 at 4:31 AM, Matthew Sleight 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJul 12, '10 at 11:38a
activeJul 12, '10 at 4:25p
posts2
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase