X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5B97853B4E for ; Wed, 27 Apr 2005 09:05:54 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 30263-10 for ; Wed, 27 Apr 2005 12:05:49 +0000 (GMT) Received: from biglumber.com (biglumber.com [207.228.252.42]) by svr1.postgresql.org (Postfix) with SMTP id 6A5D753AC6 for ; Wed, 27 Apr 2005 09:05:46 -0300 (ADT) Received: (qmail 24378 invoked from network); 27 Apr 2005 12:05:49 -0000 Received: from unknown (HELO localhost) (207.228.252.42) by 0 with SMTP; 27 Apr 2005 12:05:49 -0000 From: "Greg Sabino Mullane" To: pgsql-sql@postgresql.org Subject: Re: UPDATE WITH ORDER BY X-PGP-Key: 2529 DF6A B8F7 9407 E944 45B4 BC9B 9067 1496 4AC8 X-Request-PGP: http://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 In-Reply-To: <426DB1A0.2000604@carvalhaes.net> Date: Wed, 27 Apr 2005 12:05:49 -0000 X-Mailer: JoyMail 1.48 Message-ID: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.253 tagged_above=0 required=5 tests=AWL, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200504/388 X-Sequence-Number: 21281 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 (Please send email as text, not HTML) > I need to make an UPDATE on a column reordering it with a sequence > using order by a description > ... BEGIN; CREATE SEQUENCE fruit_seq; CREATE TABLE newfruit AS SELECT nextval('fruit_seq')::int AS newid, * FROM fruit ORDER BY lower(description); ALTER TABLE newfruit DROP COLUMN id; ALTER TABLE newfruit RENAME COLUMN newid TO id; DROP TABLE fruit; ALTER TABLE newfruit RENAME TO fruit; DROP SEQUENCE fruit_seq; COMMIT; SELECT * FROM fruit ORDER BY id ASC; - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200504270805 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFCb4ArvJuQZxSWSsgRAnSGAJwMbp6qoN3H2wFedsgn8N55kV6zUQCg77Qn VWsBmACCUFIdzRDRRalG6KI= =y3G9 -----END PGP SIGNATURE-----