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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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