swapping values in a field in one ad-hoc query??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

swapping values in a field in one ad-hoc query???

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2009-05-19 : 11:02:41

Hello,

I have a table with a column that looks like this:


Col1        Col2
1           other data
2           ...
1
2
3
2
4
...

What I'd like to know is is it possible to swap two distinct values in Col2? In other words, say I wanted to make where it reads 1 to be 2 and where it reads to be 2 to be 1. So, any data that is dependent on the number in Col1 would be swapped, because I'm swapping 2 for 1 and 1 for 2. Is it possible to do this in one query, or must I use something similar to the old trick in programming of using a temporary variable to swap the values?

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 11:09:05

sorry didnt understand what you mean by swapping ..can you illustrate with example?

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2009-05-19 : 11:28:05

Sure. To swap means to exchange or barter. Like if I give you my stapler in exchange for your pencil sharpener. We swap items. So, in this case, I'm swapping numbers, giving you a "1" in exchange for your "2".

Like I tried to do something like this but it doesn't work:

select replace(col1, 1, 2), replace(col1, 2, 1)
from table1

Is there an easy way to do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 11:31:51

so you want to change all col1 values that are currently 1 to 2 and vice versa?


UPDATE table
SET Col1=2-Col1 + 1 
WHERE Col1 IN (1,2)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-19 : 11:47:27

You can even do this as the statements are ACID complient


DECLARE @foo TABLE (
	[Id] INT IDENTITY(1,1) PRIMARY KEY
	, [valA] INT
	, [valB] INT
	)

INSERT @foo ( [valA], [valB] )
      SELECT 1, 2
UNION SELECT 3, 4
UNION SELECT 123123, 123123
UNION SELECT 9, 19

SELECT * FROM @foo

UPDATE @foo SET
	[valA] = [valB]
	, [valB] = [valA]

SELECT * FROM @foo

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2009-05-19 : 11:48:51

Right! But I'd like to do it for any two numbers like 3 and 4 or 10 and 1 or 0 and 5, like that.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-19 : 11:56:33

2 Approaches for 10,1 and 5,0


DECLARE @foo TABLE (
	[Id] INT IDENTITY(1,1) PRIMARY KEY
	, [valA] INT
	, [valB] INT
	)

INSERT @foo ( [valA], [valB] )
      SELECT 1, 2
UNION SELECT 3, 4
UNION SELECT 123123, 123123
UNION SELECT 9, 19
UNION SELECT 0, 5
UNION SELECT 5, 0
UNION SELECT 10, 1

SELECT * FROM @foo

UPDATE @foo SET
	[valA] = [valB]
	, [valB] = [valA]
WHERE
	(
		([valA] = 10 AND [valB] = 1)
		OR
		([valA] = 1 AND [valB] = 10)	
	)
	OR
	(
		CAST([valA] AS VARCHAR(255)) + CAST([valB] AS VARCHAR(255)) IN ('50', '05')
	)

SELECT * FROM @foo

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2009-05-20 : 14:13:23

That should work. Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources