CODEWITHSUNDEEP
sqlsql-server-2008
YVS1102
YVS1102

Reputation: 2748

Multiple update rows with a column as parameter

I'm trying to update multiple rows in my table:

    Nip    |       AttendanceDate  |          InTime       |           OutTime
   ----------------------------------------------------------------------------------------------
    1105321|2016-08-30 00:00:00.000|1900-01-01 00:00:00.000|1900-01-01 00:00:00.000
    1105321|2016-08-31 00:00:00.000|1900-01-01 00:00:00.000|1900-01-01 00:00:00.000
    1105321|2016-09-01 00:00:00.000|1900-01-01 00:00:00.000|1900-01-01 00:00:00.000
    1105321|2016-09-02 00:00:00.000|1900-01-01 00:00:00.000|1900-01-01 00:00:00.000

I want to update InTime & OutTime .

I know I can simply do this

update Attendance 
set InTime = '2016-08-30 08:00:00.000',
    OutTime = '2016-08-30 18:00:00.000'
where Nip = '1105321' and AttendanceDate = '2016-08-30'

But with my query, I must do it one by one. So, my question is can I update it just once? So I don't need to do the update one by one. Is it possible? Sorry for my bad English.

UPDATE:

So I don't need to do this 

update Attendance 
set InTime = '2016-08-30 08:00:00.000',
    OutTime = '2016-08-30 18:00:00.000'
where Nip = '1105321' and AttendanceDate = '2016-08-30'

update Attendance 
set InTime = '2016-08-31 08:00:00.000',
    OutTime = '2016-08-31 18:00:00.000'
where Nip = '1105321' and AttendanceDate = '2016-08-31'

update Attendance 
set InTime = '2016-09-01 08:00:00.000',
    OutTime = '2016-09-01 18:00:00.000'
where Nip = '1105321' and AttendanceDate = '2016-09-01'

update Attendance 
set InTime = '2016-09-02 08:00:00.000',
    OutTime = '2016-09-02 18:00:00.000'
where Nip = '1105321' and AttendanceDate = '2016-09-02'

Upvotes: 0

Views: 47

Answers (1)

dotnetom
dotnetom

Reputation: 24901

Assuming the data type of AttendanceDate is dateTtime, you can use simple UPDATE statement and DATEADD function:

UPDATE Attendance
SET InTime = DATEADD(HOUR, 8, AttendanceDate),
    OutTime = DATEADD(HOUR, 18, AttendanceDate)
WHERE Nip = '1105321'

The statement updates all rows where condition Nip = '1105321' is met (as defined by WHERE statement). If you want to update every row, just remove WHERE statement.

InTime is calculated by adding 8 hours to AttendanceDate, and OutTime - by adding 18 hours to AttendanceDate.

If you store the date as string, you will need to do some casting from string to datetime, and then cast back when you are done, or choose to use string manipulation.

Upvotes: 1

Related Questions