SQLTeam.com | Weblogs | Forums

Swap column data


#1

I have a table with measurement data. PK is a plant ID and a Timestamp.
Caused by a configuration error in the data logger, two columns of measurement values are swapped prior to a certain date and time.
Is there an elegant way to swap the values of those two columns ?

/PeO


#2

You can swap them like shown below (assuming both columns are of the same data type or can be converted implicitly to one another)

update yourTable set
	columnA = columnB,
	columnB = columnA
where
	YourTimestampColumn < '20150301';

Usually when I do changes that are critical, I wrap them in a transaction, and rollback or commit as required. For example:

begin tran

update yourTable set
	columnA = columnB,
	columnB = columnA
where
	YourTimestampColumn < '20150301';

select * from YourTable -- verify that the updates have been correctly applied.

rollback

Once you are convinced that the changes are indeed what you wanted, replace the rollback statement with commit and run the whole code again to make the changes permanent.


#3

Thanks. I thought it was something like this, but was not sure if a direct swap without a temp variable was possible.