Change values of two columns - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Change values of two columns

Author

Topic

SQL_Prodigy
Starting Member

4 Posts

Posted - 2012-08-09 : 06:59:26

Hi,

I have a situation where I must update two columns of table, where values from first column I must write in second column, but values from second column I must write in first column.

Basically, this is simple changing places of two values.

I know I can do this with the cursor, but I asked myself if this is possible in simple query?

SQL_Prodigy
Starting Member

4 Posts

Posted - 2012-08-09 : 07:34:09

I found solution:

DECLARE @temp nvarchar(20)

UPDATE TableName
SET @temp = SecondColumn
SecondColumn = FirstColumn
FirstColumn = @temp

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-08-09 : 07:47:26

This is a homework question and you have the wrong answer.

Jim

Everyday I learn something that somebody else already knew

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-09 : 08:59:43

create table #temp
(col1 nvarchar(100),
col2 nvarchar (100),
)

INSERT INTO #temp
SELECT 'Hendrix','Guitar'

SELECT col1 , col2 FROM #temp
--switching the values
UPDATE #temp
SET col1 = col2,
col2 = col1

SELECT col1 , col2 FROM #temp

DROP table #temp

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-09 : 10:13:41

quote:
Originally posted by SQL_Prodigy

I found solution:

DECLARE @temp nvarchar(20)

UPDATE TableName
SET @temp = SecondColumn
SecondColumn = FirstColumn
FirstColumn = @temp

did you test this?
this wont even compile

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SQL_Prodigy
Starting Member

4 Posts

Posted - 2012-08-13 : 02:23:21

Sorry visakh16,

it's my mistake, because i wrote code in this place, and forgot commas.

Valid code is:

DECLARE @temp nvarchar(20)

UPDATE TableName
SET @temp = SecondColumn,
SecondColumn = FirstColumn,
FirstColumn = @temp

and this code worked fine for me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 10:10:41

ok...but you dont need the variable at all. the other posted suggestion is enough

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources