CODEWITHSUNDEEP
postgresql
Kwahn
Kwahn

Reputation: 476

UPDATE in a specific order

So let's say I have a table:

SELECT * from test_table order by name;
----|----
name|ord
----|----
a   |4
a   |5
b   |2
c   |3
d   |1

And I want to change the ord such that it matches the alphabetized result of the "order by name" clause. My goal, therefore, is:

SELECT * from test_table order by name;
----|----
name|ord
----|----
a   |1
a   |2
b   |3
c   |4
d   |5

Is there a good way in Postgres to do this? I have a new sequence I can pull from, I'm just not sure how to do this cleanly in-place, or if that's even possible. Or should I just store the results of the selection, then iterate over and select each name, assigning a new ord value to them? (They all have unique IDs, so the repeat shouldn't matter)

Upvotes: 0

Views: 1459

Answers (1)

OBi
OBi

Reputation: 116

You don't need any sequence for this.

The first step is determinate the new data:

SELECT
  *
 FROM test_table AS test_table_old
 LEFT JOIN (
  SELECT
    *, row_number() OVER () AS ord_new
   FROM test_table
   ORDER BY name, ord
  ) AS test_table_new USING (name, ord)
;

Then convert this to an update:

UPDATE test_table SET
  ord = test_table_new.ord_new
 FROM test_table AS test_table_old
 LEFT JOIN (
  SELECT
    *, row_number() OVER () AS ord_new
   FROM test_table
   ORDER BY name, ord
  ) AS test_table_new USING (name, ord)
 WHERE (test_table.name, test_table.ord) = (test_table_old.name, test_table_old.ord)
;

If you need a new sequence, then replace "row_numer() OVER ()" to "nextval('the_new_sequence_name')".

Upvotes: 1

Related Questions