Replace values in column with Oracle

Question

How can I change all the values of a single column for other ones in one single order? For example, I want to change old values of the last column salary (2250,1,3500,1) for new ones (2352,7512,4253,1142). I have this database:

I know how to do it but changing step by step, and it is not efficient if I have a lot of rows. This way:

UPDATE TABLE tablename
   SET salary = REPLACE(tablename.salary, 2250, 2352);

and then perform that operation multiple times.

Answer 1

UPDATE TABLE tablename
SET salary = 2250
WHERE salary = 2352

I'm not sure what you're aiming for with the REPLACE() function but if you want to change the values then you need to do it like the above code. Set the salary to what you want WHERE it has a salary of 2250.

You can write it a few times with the different criteria and then run it.

EDIT: Since you're worried about doing this numerous times you can create a table called salaries:

CREATE TABLE t_salary AS 
SELECT salary from tablename;

ALTER t_salary add newsalary integer after salary;

In the 'newsalary' column you can add what the new salary should be then do an inner join. I just created a table for this purpose called stackoverflow (which would be your 'tablename'

update stackoverflow s
inner join t_salary ns on s.salary = ns.salary
set s.salary = ns.newsalary;

Now what this will do is join tablename to t_salary where the current salary = the salary in t_salary. Then you set the tablename.salary equal to the new salary, this worked for me I hope it works for you.

Note, the syntax may be slightly different since I don't have Oracle installed on my home machine, I used MySQL.