Change the value order of a column-CodePudding

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)

CodePudding user response：

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