Alban Hertroys skrev:
> Nis Jørgensen wrote:
>> Alban Hertroys skrev:
>>> Would something like
>>> UPDATE master set m2 = master2.m2
>>> FROM (
>>> SELECT m2 +1
>>> FROM master m
>>> WHERE m.master_id = master.master_id
>>> ORDER BY m2 DESC
>>> ) master2
>>> work? I think it might be faster (and possibly cause less index bloat)
>>> than doing two consequent updates.
>> I don't understand your query. I don't think you can use a correlated
>> subquery in that way.
> Hmm indeed, it complains something vague: "ERROR: subquery in FROM may
> not refer to other relations of same query level". Not sure why?
As I said, I don't understand what you think it does. What you are doing
is similar to writing
FROM master, (
FROM master m
WHERE m.master_id = master.master_id
Which doesn' make any sense either.
You probably want
UPDATE master set m2 = master2.m2
SELECT m2 +1
FROM master m
ORDER BY m2 DESC
WHERE master2.master_id = master.master_id
> Effectively it orders the updates descending, so that the new value of
> m2 can never be updated to an already existing value, because that has
> been updated previously.
> The WHERE condition makes the query look a bit more complex than it
> actually is, but is necessary of course.
>> Anyway, tricks like these might work. They might stop working without
>> warning, if the plan changes. Relying on unspecified behavior is a
>> recipe for trouble.
> If I specifically ask for an ordering, I don't think the planner should
> change or ignore that ordering. So I'm not relying on unspecified behaviour.
According to the SQL spec, all the updates happen at the same time. Thus
any order this happens in is an implementation detail.
The only places where an "ORDER BY" clause is guaranteed to yield
specific results are those which are documented. - off the top of my
head, I can think of "outputting rows to the client", "LIMIT" and
The fact that you stick an "ORDER BY" into a subquery guarantees
nothing. The planner might even see that it has no effect (according to
the spec) and ignore it. For instance this
FROM (SELECT *
ORDER BY id
is not guaranteed to return an ordered result set. Thus the planner can
ignore the ORDER BY (but might not do so).
In response to
pgsql-general by date
|Next:||From: Alban Hertroys||Date: 2007-09-28 11:39:12|
|Subject: Re: Why the ERROR: duplicate key violates unique constraint "master_pkey"
is raised? - Is this a Bug?|
|Previous:||From: Alban Hertroys||Date: 2007-09-28 10:16:54|
|Subject: Re: Re: Why the ERROR: duplicate key violates unique constraint
"master_pkey" is raised? - Is this a Bug?|