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.
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.
SELECT distinct salary from table name If you have more than 10 then you would certainly need a function or procedure, I'll get back to you when I find something.Since you already a list old salary values and their corresponding new salary values you can place them in a flat file and create an external table in oracle to point to this file.
Once that is done then you can just fire a simple update statement similar to the one given below:
update test1 set salary = ( select newsalary from test2 where test1.empid = test2.empid);
UPDATE tablename SET salary = 2250 WHERE salary = 2352
This works fine for Oracle SQL.
