On Thu, Feb 12, 2009 at 05:39:49PM +0000, Rory Campbell-Lange wrote: > On 12/02/09, Rory Campbell-Lange (rory@xxxxxxxxxxxxxxxxxx) wrote: > > I realise that for every row in my users table (which has a unique > > integer field) I can update it if I construct a matching id field > > against a random row from the testnames table. > > I can make my join table pretty well by using the ranking procedures > outlined here: http://www.barik.net/archive/2006/04/30/162447/ > > CREATE TEMPORARY SEQUENCE rank_seq; > select nextval('rank_seq') AS id, firstname, lastname from testnames; [...] > Any other ideas? The first is similar to the best I could come up with as well. Your problem is difficult to express in SQL because what you're trying to do doesn't seem very relational in nature. I'd do something like: BEGIN; ALTER TABLE users ADD COLUMN num SERIAL; CREATE TEMP SEQUENCE s1; UPDATE users u SET name = x.name FROM ( SELECT name, nextval('s1') AS id FROM ( SELECT name FROM testnames ORDER BY random() OFFSET 0) x) x WHERE u.id = x.id; ALTER TABLE users DROP COLUMN num; COMMIT; If your existing unique integer field runs from 1 to a number less than the number of testuser names then you won't need to add the "num" column first. The inner selects are about making sure that things are ordered randomly before we assign a sequence value to them, not sure if it's strictly needed but shouldn't hurt. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
