CODEWITHSUNDEEP
sqlsql-server
Danger_Fox
Danger_Fox

Reputation: 449

How to increment an int column on multiple rows

I'm attempting to add one to a column in a table for IDs that meet a certain criteria. Is there any way to do this without selecting that column for each ID?

Here is what I've attempted:

update AttendanceRecordReporting set Penalty8 = Penalty8 + 1 where ID = 
  (select ID from Employee where ID not in 
   (select distinct CWID from AttendanceRecord where RecordDate between 
    '3/1/2014 12:00:00 AM' and '6/1/2014 12:00:00 AM') and Department = 1)

The error I receive

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Upvotes: 0

Views: 37

Answers (1)

VJ Hil
VJ Hil

Reputation: 904

USE 'IN' operator instead of '='

update AttendanceRecordReporting set Penalty8 = Penalty8 + 1 
      where ID IN 
           ( 
              select ID from Employee where ID not in 
                 (
                  select distinct CWID from AttendanceRecord where RecordDate between 
                 '3/1/2014 12:00:00 AM' and '6/1/2014 12:00:00 AM'
                  ) 
             and Department = 1
           )

Upvotes: 3

Related Questions