Swap data between columns 2

[scriptscribe]

Hi all,

Someone loaded data into a table into the wrong column and I need to swap the data from Code into CodeDesc and from CodeDesc into Code.

My table looks like this:

PK HospFK Code CodeDesc
2091 1111 Self-Pay 0
2092 1111 Bad Debt A 5
2093 1111 Bad Debt NA 6
2094 1111 Bad Debt M 7

I know how to do a simple update query like this:

UPDATE HospFinClassCodes
SET Code = CodeDesc
where HospFK = 1111

...but I don't know how to go about swapping the two.

Any help will be greatly appreciated.

Thanks,
Mike

 

[SQLSister]

The one way is to copy the data into a third column from one column (creating it if youhave no handy empty column to use). Then update that column with col2, then update col2 with col3 (which has the orginal contrents of col1) then delete col3 (or delte the data in col3.

You can also do this with temp tables, pull the rcords you want to change into a temp table. Then do the update with a join to the temp table to set col1 = temptable.col2 and col2 = temptable.col1

Questions about posting. See faq183-874

 

[gmmastros]

There's no need to mess around with a 3rd column. Take a look at this...

[COLOR=blue]DEclare[/color] @Temp [COLOR=blue]Table[/color](Col1 [COLOR=blue]int[/color], Col2 [COLOR=blue]int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] @Temp [COLOR=blue]Values[/color](1,100)
[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] @Temp [COLOR=blue]Values[/color](2,300)
[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] @Temp [COLOR=blue]Values[/color](3,300)

[COLOR=blue]Select[/color] * [COLOR=blue]From[/color] @Temp

[COLOR=blue]Update[/color] @Temp
[COLOR=blue]Set[/color]    Col1 = Col2,
       Col2 = Col1

[COLOR=blue]Select[/color] * [COLOR=blue]From[/color] @Temp

The output...

[tt][blue]
Col1 Col2
----------- -----------
1 100
2 300
3 300

(3 row(s) affected)


(3 row(s) affected)

Col1 Col2
----------- -----------
100 1
300 2
300 3

(3 row(s) affected)
[/blue][/tt]


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[scriptscribe]

Thanks for the helpful replies.

I ended up using a hybrid version of what both of you posted.

SELECT * 
INTO #HospFinClassCodesTemp
From HospFinClassCodes

Update #HospFinClassCodesTemp
Set Code = CodeDesc, CodeDesc = Code
where Hospitalfk = 1280

Select * from #HospFinClassCodesTemp
where Hospitalfk = 1280

 

[micang]

Maybe an option could also be to rename the columns themselves?

Not sure if this would implicate things.

Michael

 

[scriptscribe]

The problem with doing that is that there are about 10 rows in a 10,000 row table with this issue. In other words, it's just the rows with the hospitalfk = 1280.

 

[micang]

Ahh, yes, that would not work!

I did not realise that. Glad you got sorted.

Michael