title: Updating tables from generate_series(int, int) function Postgres 9.0
body: <p>I have a table with data in it I want to keep, but I have to add a new column of integers for ordering purposes. Now this ordering will be different depending on the clientID as each different client wants different ordering. So in my example there are 3 different clients, the first client has 10 rows of data the second has 15, and the third has 87. So basically I'm looking for a query that will let me update the ordering column in a way that will allow me to do a select on the table that would give results like this. </p>
  
  <pre><code>Select ordering from table Where clientID = 1 
  -----------
  Ordering
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  </code></pre>
  
  <p>Now the query I'm currently using to do this is </p>
  
  <pre><code>UPDATE data SET ordering = generate_series
  FROM (SELECT * FROM generate_series(1,87)) as k &lt;
  where  clientid = '3'
  </code></pre>
  
  <p>This will update all the correct rows but only with the first value, so all the values in ordering would be 1. I feel like I'm missing something here or this just doesn't work in postgres as it does in other SQL languages. Any solution here will help I would also like to know why my update would not work as I expected in postgres. Also I cannot change versions of postgres based on the environment I work in.</p>