Solved

# Swapping (replacing) values in one column with another column.

Posted on 2003-03-25
Medium Priority
366 Views
I have the following 2 issues in regards to replacing values in columns.

Issue 1
-------------
Table A
-Column 1
-Column 2

I would like to swap the values between columns for all rows in Table A. That is, Column 2 data is moved to Column 1 and Column 1 data is moved to Column 2.

Issue 2
-------------
Table B
- Column 1
- Column 2

I would need the value for Column 1 moved to Column 2 and Column 1 replaced with the Column 1 value from Table A above after it's data has been adjusted. It will need to match the value from Table B Column 2 with Table A Column 2 (after Table B's Column values have been adjusted).  When a match is found the Column 1 value from Table A would be inserted into Column 1 in Table B.

0
Question by:faron
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 1

Expert Comment

ID: 8205446
If it is for one column at a time this will work....

declare @value_1
declare @value_2

set @value_1 = column 1 from table A
set @value_2 = column 2 from table A

update table A (column 1, column 2)
values @value_2, @value_1)

and if you want to make sure they are the same values in table b just do a second update

update table B (column 1, column 2)
values @value_2, @value_1)

or if you want to run the whole thing a second time to update table b do this.....

declare @value_1
declare @value_2

set @value_1 = column 1 from table A
set @value_2 = column 2 from table A

update table B (column 1, column 2)
values @value_1, @value_2)
0

LVL 69

Accepted Solution

Scott Pletcher earned 1000 total points
ID: 8205461
Issue 1:

UPDATE tableA
SET column1 = column2, column2 = column1

Issue 2, in one statement:

UPDATE tableB
SET b.column2 = b.column1,
b.column1 = ISNULL(a.column1, '')
FROM tableB b
LEFT OUTER JOIN tableA a ON b.column1 = a.column2

--note that since column1 isn't copied to column2 until the UPDATE runs,
--the join must be based on column1, but, after the join,
--the value for b.column1 can be set to the value of a.column1
0

LVL 69

Expert Comment

ID: 8205483
The key is that SQL uses the original value for all references to a column in an UPDATE.

For example, in the first UPDATE, even though column1 is set first, the second set (c2 = c1) still works because SQL uses the original value of column1 in the second set.  Likewise for the second UPDATE.
0

LVL 34

Expert Comment

ID: 8207520
Issue 1

If these are large tables, instead of doing a big update query, you might just rename the columns (if there aren't dependencies).

sp_rename 'dbo.table1.column1','tmpcolumn1','column'
sp_rename 'dbo.table1.column2','tmpcolumn2','column'
sp_rename 'dbo.table1.tmpcolumn1','column2','column'
sp_rename 'dbo.table1.tmpcolumn2','column1','column'

Issue 2 as scottpletcher suggested...

Brett

0

Author Comment

ID: 8211756
ScottPletcher - Your suggestions for issue 1 and 2 work, however when running the Issue 1 solution, I get an error message about duplicate entries in index table1.  If I manully remove the index it works.  Is there a way to sript in the removal of the index's for this table before I exexute the change?

Arbert- Unfortunatly I cannot rename the columns as I have to have a conditional clause to only alter specific rows in the table.  Thanks for the tip though.

0

Author Comment

ID: 8221758
Thanks,

It works well.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
###### Suggested Courses
Course of the Month9 days, 17 hours left to enroll