Swap columns from two sql server tables

Question

I would like to know if there is anyway I can compare two columns in SQL Server.

The two columns are located in two different tables.

When the column 1's value is smaller than the column 2's value:
I want to replace the value of the column 1 with the value of the column 2.

Answer 1

You should be able to do something like this:

update tablename set column1=column2
from table1 inner join table2 on joincondition
where column1 < column2;

Hard to be more spesific without the actual table structure.

Answer 2

update table1 t1
set    t1.col1 = (select t2.col2
                  from   table2 t2
                  where  t2.id = t1.id
                  and    t1.col1 < t1.col2)

Something like that should do it easily.

The only tricky point I see is matching the row from table2 to the row from table1. In my example, I supposed both tables share an unique "id" column which enables easy matching. Modify the query with something more appropriate.