CODEWITHSUNDEEP
sqlsql-server-2005
user2165345
user2165345

Reputation: 67

SQL Server 2005 update query based on same table

I have a table with the following fields: ID_Observation (PK), ID_Grading, ID_ObKind, Data.

I'm having difficulty writing a query to update some rows based on other rows within the table.

If ID_ObKind = 9 AND Data = 'No' then any row which has the same ID_Grading with ID_ObKind = 10 and Data = NULL needs to be updated to Data = 0.

I have about a dozen different cases where data needs to be changed depending on the value of Data for a specific ID_ObKind so need to nail this down. Each ID_Grading can have up to 120 rows, each with a different ID_ObKind.

Upvotes: 2

Views: 309

Answers (1)

David W
David W

Reputation: 10184

I think this will work for you. http://sqlfiddle.com/#!3/2eeaf/14

Update TheTable
   set data=0
 where id_grading = (select id_grading
                       from TheTable
                      where id_obkind=9
                        and data='No')
   and id_obkind=10
   and data is null

Upvotes: 1

Related Questions