Question 20153 in Mysql Question posted in
The official MySQL documentation can be found here.

How to add unique keys in a new column for repeated values in a different column within SQL? – Mysql

I have a table with a column with repeating values, and I want to add another column that has unique values for each distinct value in the first column.Here is a sample:

temp  Postal
1     42420
2     90032
3     60610
4     42420
5     98661
6     10024
7     10024
8     10024

Considering the above example, row 1 and 4 should have the same value , while 6,7 and 8 should share a value as well.

I wrote a query that implements my intended feature but on a limited scale:

UPDATE `train` 
SET `new_column`=(SELECT MIN(temp) FROM train WHERE `Postal`= '42420')
 WHERE `Postal`='42420';

This works well but is unscalable for large datasets(the one I am using has 9801 rows). Is there away to make it work automatically for the whole table?

Tags:

2

Answers


  1. 0

    If you’re running MySQL 8+, or a recent version of MariaDB, then I suggest not doing this update. The reason is that any time your data changes, e.g. by adding a new record with the same Postal value, you might be forced to run the update again. Instead, just use the following select query:

    SELECT temp, Postal, MIN(temp) OVER (PARTITION BY Postal) AS new_column
FROM train
ORDER BY temp;

    You could place this query into a view for easy access. This query should benefit from the following index:

    CREATE INDEX idx ON train (Postal, temp);
    Login or Signup to reply.
  2. 0

    You can do it using group by to get the smallest temp by Postal, then using inner join to get the smallest temp per record :

    update train t
inner join (
  select Postal, min(temp) as min_temp
  from train
  group by Postal
) as s on s.Postal = t.Postal
set t.new_column = s.min_temp

    Result :

    temp    Postal  new_column
1       42420   1
2       90032   2
3       60610   3
4       42420   1
5       98661   5
6       10024   6
7       10024   6
8       10024   6

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search