Re: Table update problem works on MySQL but not Postgres

On Tue, Aug 31, 2010 at 07:56:23PM -0400, Raymond C. Rodgers wrote:
>  Let me stress that this is not a bug in PostgreSQL; if anything at
> all, it's only a lack of a stupid feature.

PostgreSQL's version involves UPDATE ... FROM.  Use an ORDER BY in the
FROM clause like this:

UPDATE mydemo SET cat_order = m.cat_order+1
FROM (
    SELECT cat_order, client_id
    FROM mydemo
    WHERE
        client_id = 1 AND
        cat_order >= 0
    ORDER BY cat_order) m
WHERE
    mydemo.cat_order = m.cat_order AND
    mydemo.client_id = m.client_id

More details on PostgreSQL's UPDATE are at:
http://www.postgresql.org/docs/current/static/sql-update.html

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate