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: 4F7AEF1E0200002500046B35@gw.wicourts.gov (view raw or flat)
Thread:
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
from
     upd_temp1 t
where
     t.id_number = t2.id_number
 
-Kevin

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-2014 The PostgreSQL Global Development Group