CODEWITHSUNDEEP
c#.netsql-server-2005
Nazmul
Nazmul

Reputation: 7218

Rearrange Data in Columns under specific condition in SQL

I have a table where the following data is available:

ProductID   ProductName Pref
---------   ----------- ----
001        ABC            1
005        CDE            3
009        EFG            2
008        HIJ            4
003        IJK            5

I would like to update [Pref] of [ProductID] with "005" fromm 3 to 5. It is easy by using UPDATE SQL command. But [Pref] column is unique and I can not do such SQL command to update.

What I did: Sorting [Pref] 

ProductID   ProductName Pref
---------   ----------- ----
001        ABC              1
009        EFG              2
005        CDE              3 <---
008        HIJ              4
003        IJK              5

What I want to see my data as follows:

ProductID      ProductName  Pref
---------   -  ----------   ----
001          ABC             1 -->1
009          EFG             2 -->2
008          HIJ             4 -->3
003          IJK             5 -->4
005          CDE             3 -->5<--

What should be the SQL command to do such change.

I am using C# .NET (IDE:Visual Studio 2005) and MS SQL SERVER 2005.

Thank you so much for your help.

Upvotes: 1

Views: 92

Answers (3)

gbn
gbn

Reputation: 432471

UPDATE T
SET Pref = 
    CASE 
       WHEN Pref > 3 THEN Pref-1
       WHEN Pref = 3 THEN 5
    END
WHERE
   Pref =>= 3;

Upvotes: 0

cjk
cjk

Reputation: 46465

You could do it like this:

UPDATE Table
SET Pref = -1 
WHERE Pref = 3

UPDATE Table
SET Pref = Pref - 1 
WHERE Pref > 3

UPDATE Table
SET Pref = 5
WHERE Pref = -1

With a bit of parameterization and searching for the highest current number, this could become quite generic.

Edit (see comments):

UPDATE Table 
SET Pref = CASE WHEN Pref = 3 THEN 5 ELSE Pref - 1 END
WHERE Pref >= 3

Upvotes: 3

Royi Namir
Royi Namir

Reputation: 148644

change the XXX to your needs

select peoductId , prodductname ,row_number() over (oredr by XXX) as pref from Table

Upvotes: 0

Related Questions