CODEWITHSUNDEEP
sql-serversql-update
jose
jose

Reputation: 2543

Update rows with composite primary key in SQL Server

I want to update every row by increasing 1 on the Ordering field, for every row that has Ordering GREATER OF EQUAL than some value but not for a specific row (IdClass=15,IdContent=9, for example - both are primary keys)

Like

UPDATE Content_Class 
    SET Ordering=Ordering+1 
WHERE Ordering>= 4 
AND (IdClass <> 15 AND IdContent <> 9)

What happens is that, both (IdClass=15,IdContent=8) and (IdClass=15,IdContent=9) are ignored is this query.

How can I do this with just one query?

Upvotes: 0

Views: 95

Answers (2)

M.Ali
M.Ali

Reputation: 69494

UPDATE Content_Class 
    SET Ordering=Ordering+1 
WHERE Ordering>= 4 
AND (IdClass <> 15 OR IdContent <> 9)

OR

UPDATE Content_Class 
    SET Ordering=Ordering+1 
WHERE Ordering>= 4 
AND IdClass NOT IN (15,9)

Upvotes: 1

Arion
Arion

Reputation: 31239

Maybe something like this:

UPDATE Content_Class 
SET Ordering=Ordering+1 
WHERE Ordering>=4 
AND NOT(IdClass = 15 AND IdContent = 9)

Upvotes: 1

Related Questions