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

Posted on 2003-03-25
Medium Priority
Last Modified: 2012-05-04
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.

Question by:faron
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
  • Learn & ask questions

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)
LVL 69

Accepted Solution

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

SET column1 = column2, column2 = column1

Issue 2, in one statement:

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
LVL 69

Expert Comment

by:Scott Pletcher
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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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...



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.


Author Comment

ID: 8221758

It works well.

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

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

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question