• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

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

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
faron
Asked:
faron
1 Solution
 
AycexCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
arbertCommented:
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
 
faronAuthor Commented:
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
 
faronAuthor Commented:
Thanks,

It works well.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now