How to increment an int column on multiple rows

Question

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.

VJ Hil · Accepted Answer · 2014-11-08 00:29:04Z

3

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
           )

answered Nov 8, 2014 at 0:29

VJ Hil

9046 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to increment an int column on multiple rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related