Change the value order of a column

Question

i have a table that i created but inserted the wrong values. The actual table is much longer but this will suffice:

id	salary
1	100
2	200
3	400
4	300

and i would like to change the order of all the values of the column salary. So basically:

id	salary
1	300
2	400
3	200
4	100

As of now the table is not tied to any constraints. What query would i need to use be able to change the order of the values of a column using a subquery (so that i don't have to manually update all the values)? The data just has to be in reverse order.

I thought of a query like this but it doesnt work:

UPDATE `t1` 
SET `Salary` = 
    (SELECT `Salary` 
    FROM `t1`
    WHERE `t1`.`id` = `t1`.`id`
    ORDER BY `Salary` DESC);

How should the query look like?

(i am using MySQL 5.7)

What's the logic for the changed order? Is it just the reverse of the original order? — Barmar, Mar 30 '22 at 14:38
An id is just an id. Reordering them can create a true mess. — jarlh, Mar 30 '22 at 14:41
Are you using MySQL 8.0? I think window functions would be helpful. — Barmar, Mar 30 '22 at 14:41
Unless the IDs are sequential, this will be difficult in a single query. You could write a script to get arrays of IDs and salaries, then reverse the salary array and do all the updates in a loop. — Barmar, Mar 30 '22 at 14:46
@jarlh not in this case, but it would be nice to know how the query would change in that case. I think i would use an inner join but im failing miserably working with the sql syntax — Rarowcun, Mar 30 '22 at 14:47

nbk · Accepted Answer · 2022-03-30T15:12:43.567

Fisz you need a second table with the new data, not all id must be present if they don't change. As you can not provide an alghorithm which would define the odrdr of the salary, you need a second table, which you can populate.

I used here a temporry table, which will 0only exist in the session, but you canuse a fixed table which you truncate(delet all rows) anfd then fill up

CREATE TEMPORARY TABLE temtable (
  `id` INTEGER,
  `salary` INTEGER
);

INSERT INTO temtable
  (`id`, `salary`)
VALUES
  ('1', '300'),
  ('2', '400'),
  ('3', '200'),
  ('4', '100');

CREATE TABLE origtable (
  `id` INTEGER,
  `salary` INTEGER
);

INSERT INTO origtable
  (`id`, `salary`)
VALUES
  ('1', '100'),
  ('2', '200'),
  ('3', '400'),
  ('4', '300');

UPDATE origtable o
INNER JOIN  temtable t ON t.id = o.id
SET o.salary = t.salary

SELECT * FROM origtable

id | salary
-: | -----:
 1 |    300
 2 |    400
 3 |    200
 4 |    100

DROP TABLE temtable

db<>fiddle here

seems like the only way to go. Thanks – Rarowcun Mar 30 '22 at 16:39 — Rarowcun, Mar 30 '22 at 16:39

Change the value order of a column

1 Answers1