lets assume I have a table with this entries:

 thing:               position:

  ball                   1001.25
  pensil                 1001.0625
  car                    1002.5
  watch                  1003.0035

And I want to run a function or query over the whole position column to change the numbers and remove the decimal point but keep the type as numeric type and keep the same positions (ordering). So I want to look it like the following after the update:

I want the table to look like this after the query: -->

thing:               position:

  ball                    2
  pensil                  1
  car                     3
  watch                   4

so when I order by position asc I still get the right order but I cleaned up the decimal points.

pensil                      1
  ball                      2
  car                       3
  watch                     4

What is a good approach to make this in order to refresh the whole position column?

That is my Table:

create table numeric_test (
   thing text,
    position numeric(1000,993) unique,

  • What would you want to see if 2 thing's had the exact same position value? Please update your question to show which RDBMS (SQL Server, Oracle, etc.) you are using. – Scott Hodgin Jul 8 '19 at 12:28
  • they can't because i use unique constraint i use postgres – DataLordDev Jul 8 '19 at 12:31

You can use an UPDATE joined to a query:

Assuming there is a primary key column named id, you can do the following:

update the_table
   set position = t.rn
from (
   select id, 
          row_number() over (order by position) as rn
   from the_table
) t
where t.id = the_table.id;

Obviously you need to adjust the table name and the column name for the primary key

  • thank you this is working. What is t.rn=? and what is t? i don't understand how the query works could you explain it :) – DataLordDev Jul 8 '19 at 13:07
  • t is a table alias for the derived table that calculates the row number. And t.rn = the_table.id is the required join for the update statement – a_horse_with_no_name Jul 8 '19 at 13:09
  • that's a very nice solution to this are there any other variants to do this? – DataLordDev Jul 8 '19 at 13:17
  • is there any way to make the update query faster when i have for e.g. 10 million entries? – DataLordDev Jul 8 '19 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.