SQL UPDATE statement to switch two values in two rows

I'm using SQL Server to swap two values in two rows. Let me show:

[ord] [name]
1     John
4     Jack
7     Pete
9     Steve
11    Mary

Say, I need to swap [ord] numbers for "Pete" and "Steve" to make this table to be like so:

[ord] [name]
1     John
4     Jack
9     Pete
7     Steve
11    Mary

This seems like a trivial task but I can't seem to write an SQL UPDATE statement for it.

Answers


If 'Peter' and 'Steve' are unique in your table, this will do:

UPDATE TableX
SET ord = ( SELECT MIN(ord) + MAX(ord) 
            FROM TableX 
            WHERE name IN ('Peter', 'Steve')
          ) - ord
WHERE name IN ('Peter', 'Steve')

or (improved by @Erwin):

UPDATE TableX
SET ord = ( SELECT SUM(ord) 
            FROM TableX 
            WHERE name IN ('Peter', 'Steve')
          ) - ord
WHERE name IN ('Peter', 'Steve')

Need Your Help

Magento: Displaying product image in order email

image magento caching order product

To show product image in order email, I have written the following code in template/email/order/items/order/default.phtml

Magento Cart Price Rule - Buy Any Combination of 5 SKU's, Get one Free! #FAIL

magento condition shopping-cart magento-1.8

I've tried every combination of conditions and actions available but can't seem to crack this. Should be simple. Any help would be much appreciated.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.