swap two columns values - 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 2000 Forums

SQL Server Development (2000)

swap two columns values

Author

Topic

etietje
Starting Member

24 Posts

Posted - 2003-04-08 : 17:09:34

Hello everybody. I need to swith two columns values under a certain situation. I imagine something along these lines, but SQL probably doesn't have this functionality.

update MyTable
SWAP MyColumnA and MyColumnB
where Some Situation Occurs

If there is no statement similar to SWAP, is there an easy way to do what I need to do?

Thanks in advance to anybody who can help!

Edited by - etietje on 04/08/2003 17:16:27

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-08 : 18:16:10

This is new to me. What's the column type? Varchar?

DECLARE @MyTemp VARCHAR

UPDATE MyTable
SET @MyTemp = MyColumnA, MyColumnA = MyColumnB, MyColumnB = @MyTemp
WHERE .....

This worked in my test, but I haven't read anything that says SQL must perform the operations in the order presented.

Sam

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-04-08 : 18:21:56

Should be something like this:


 CREATE TABLE #MyTable(RowID INT, Field1 VARCHAR(50), Field2 VARCHAR(50))

INSERT INTO #MyTable(RowID, Field1, Field2) VALUES(1, 'First Field', 'SecondField')
INSERT INTO #MyTable(RowID, Field1, Field2) VALUES(2, 'First Field', 'SecondField')
INSERT INTO #MyTable(RowID, Field1, Field2) VALUES(3, 'First Field', 'SecondField')

UPDATE #MyTable
SET Field1 = Field2, Field2 = Field1
WHERE RowID = 2

SELECT * FROM #MyTable

DROP TABLE #MyTable

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

etietje
Starting Member

24 Posts

Posted - 2003-04-09 : 13:04:47

Thanks a lot both of you. I really appreciate the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources