CODEWITHSUNDEEP
sqloracle-database
NuValue
NuValue

Reputation: 463

Replace values in column with Oracle

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:

enter image description here

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.

Upvotes: 1

Views: 4337

Answers (3)

Amey Chitari
Amey Chitari

Reputation: 1

UPDATE tablename SET salary = 2250 WHERE salary = 2352

This works fine for Oracle SQL.

Upvotes: -1

Sra1
Sra1

Reputation: 670

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);

Upvotes: 0

ZeroBased_IX
ZeroBased_IX

Reputation: 2727

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.

Upvotes: 3

Related Questions