How to swap a row of data with another

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

General SQL Server Forums

New to SQL Server Programming

How to swap a row of data with another

Author

Topic

mcskiver
Starting Member

2 Posts

Posted - 2006-06-27 : 09:08:53

Hi,

I'm pretty new to SQL and certainly new to SQL Server, so this is no doubt a simple question, but how can I take a row of data (say row 117) and swap it with another specified row of data? I know it sounds odd, but the key for each row is automatically generated so I don't want to mess with that, so I just want to swap the other fields.

Here's an example of what I mean:

----------------------------------------------
ID | headline | content | newstype | date |
----------------------------------------------
117 | test | asdfgasd| industry | 06/06|
118 | test2 | adsfg as| company | 06/06|
----------------------------------------------

What i've discovered is that the order of data in the rows isn't correct, so I want to maintain the ID fields seeing as they're in numeric order, but then use that ID to grab the rest of the row (117 for example) and replace the data for 118 with 117's data. Then do the same with 118's original data....

I need to do this because all the related rows in my table were posted on the same date, so other than that they appear on screen based on where they appear in the table, which doesn't match what the client wants...

Sorry for the long post, but as a beginner with SQL I wanted to make sure I made sense!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-06-27 : 10:02:01

What order does that client want? Are you aware you can use ORDER BY in your SELECT statement to present the data in any order you want based on any combination of columns?

Be One with the Optimizer
TG

mcskiver
Starting Member

2 Posts

Posted - 2006-06-27 : 11:51:56

Thanks for the reply. I want to be able to swap them based on client preference, rather than any field. I can't use ORDER BY because I don't actually have a column in the table that meaningfully differentiates between the records.

A number of records were published yesterday with the same date, and apart from that it's just the text that's different. I've used the Enterprise Manager to copy the required records and paste them as new rows in the correct order, then updated the text fields. Messy and time-consuming, but got the job done!

Thanks for the response.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 12:07:31

Does the ID field have an associated foreign key?

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-06-27 : 12:29:23

-- swap 117 and 118
set identity_insert tblName on
update tblName set ID = case ID when 117 then 118 else 117 end
where ID in(117,118)
set identity insert tblName off

"I've used the Enterprise Manager to copy the required records and paste them as new rows in the correct order, then updated the text fields"
Have you considered going over to excel?

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2006-06-27 : 12:34:47

Excel is not a good database you need to use word.