CODEWITHSUNDEEP
sqldatabaseoracle-database
chubbsondubs
chubbsondubs

Reputation: 38676

How can I write a SQL statement to update a column in a table from another column in the same table?

I have an oracle DB where a program is writing into two columns when it updates the table. The 2nd column is based on the value from the 1st column. Well over time people have hand edited the database and forgot to insert values into the 2nd column. I'd like to write a simple sql statement that updates all columns and syncs the 2nd column to the 1st column. I know there's some simple statement to do this. Doing a little googling I thought something like the following:

UPDATE suppliers 
SET supplier_name = ( 
    SELECT customers.name 
    FROM customers 
    WHERE customers.customer_id = suppliers.supplier_id
) 
WHERE EXISTS ( 
    SELECT customers.name 
    FROM customers 
    WHERE customers.customer_id = suppliers.supplier_id
);

However, that is between 2 different tables where I would be doing it on the same table.

Upvotes: 0

Views: 2622

Answers (3)

dpbradley
dpbradley

Reputation: 11915

update tableName set col2 = col1

Upvotes: 2

Abe Miessler
Abe Miessler

Reputation: 85036

The following works in SQL Server (haven't checked Oracle yet). 

UPDATE SUPPLIERS SET Supplier_Name = CustomerName

I'd give this a try and see if it works...

Upvotes: 3

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

If both columns are in the same table, you can use the simplest query:

UPDATE your_table 
   SET column1 = column2
 WHERE column1 != column2;

This supposes that both columns are NOT NULL. If the columns are nullable however, use decode instead:

UPDATE your_table 
   SET column1 = column2
 WHERE decode(column1, column2, 1, 0) = 0;

Upvotes: 2

Related Questions