CODEWITHSUNDEEP
sqlsql-server
jwolf
jwolf

Reputation: 69

Nested WHERE clauses in one SET Statement

I'm trying to display information in a certain order from a table pulled through SQL. I am doing so through a column called OrderID, which takes an integer as its property. A table has rows populated in ascending order of the OrderID. The user has the option to delete a row in the table. Also, multiple users can have identical OrderIDs, but there is a unique identifier (ID) for each row. So, when trying to delete, I identify the row to be deleted based on the unique ID.

string query = "DELETE FROM dbo.ExampleDB WHERE ID = @ID";

Before I delete this row from the table, I'd like to lower all OrderIDs by one if they are greater than the OrderID that will be deleted. I'm attempting to do this by using this query:

string query2 = "UPDATE dbo.ExampleDB SET OrderID = (OrderID - 1) WHERE OrderID > (OrderID WHERE ID = @ID)";

As you can see, I subtract 1 from the integer value OrderID. It is essential for me to keep all OrderIDs an increment of 1 apart, with no gaps. This code is giving me the error "Incorrect syntax near the keyword 'Where'", referring to the second WHERE in query 2. Is there a way to have more than one WHERE clause for one SET statement? Would there be a better way to do what I'm attempting?

Upvotes: 1

Views: 52

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726509

You should be able to do it by using an additional SELECT, assuming that a single row is returned:

string query2 = @"
    UPDATE dbo.ExampleDB
    SET OrderID = (OrderID - 1)
    WHERE OrderID > (
        SELECT OrderID FROM dbo.ExampleDB WHERE ID = @ID
    )";

Upvotes: 2

Related Questions