Skip site navigation (1) Skip section navigation (2)

Re: Update join performance issues

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Kevin Kempter" <cs_dba(at)consistentstate(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Update join performance issues
Date: 2012-04-03 17:37:50
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Kevin Kempter <cs_dba(at)consistentstate(dot)com> wrote:
> update test_one
> set f_key = t.f_key
> from
>      upd_temp1 t,
>      test_one t2
> where
>      t.id_number = t2.id_number
As written above, it is joining the two table references in the FROM
clause and updating every row in test_one with every row in the JOIN
-- which is probably not what you want.  Having a FROM clause on an
UPDATE statement is not something which is covered by the standard,
and different products have implemented different semantics for
that.  For example, under MS SQL Server, the first reference in the
FROM clause to the target of the UPDATE is considered to be the same
reference; so the above statement would be accepted, but do
something very different.
You probably want this:
update test_one t2
set f_key = t.f_key
     upd_temp1 t
     t.id_number = t2.id_number

In response to

pgsql-performance by date

Next:From: Andrew DunstanDate: 2012-04-03 17:39:05
Subject: Re: Update join performance issues
Previous:From: Kevin KempterDate: 2012-04-03 17:29:56
Subject: Update join performance issues

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group