YVS1102 YVS1102 - 3 months ago 9
SQL Question

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'

Answer

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.