update is not working using join in mysql-CodePudding

I’m facing a problem in SQL Join and Update. Have tried different combinations of ">=" / "=" / "<", but never obtaining the intended result.

Table: 'punchdata'
Id      empID    pType        pDate       pTime     pInOut    DeleteRec 
32382   EMP-217  Attendance   2021-11-26  08:50:45  In        0
33540   EMP-217  Attendance   2021-11-26  12:34:21  Out       0
33171   EMP-217  Attendance   2021-11-26  17:13:49  Out       0
33170   EMP-217  Attendance   2021-11-26  17:09:49  Out       0
33168   EMP-217  Attendance   2021-11-26  17:11:18  In        0
33036   EMP-217  Attendance   2021-11-26  12:34:32  Out       0

SQL Statement:
update punchdata w1
  join ( select min(id) as SIMPLEID, `empID`, `pDate`, `pTime`
  from punchdata where pType = 'Attendance' AND pDate = '2021-11-26'
  group by `empID`,`pDate`,`pTime` ) w2
on w1.id > w2.SIMPLEID
and w1.`empID` = 'EMP-217'
and w1.`pDate` = w2.`pDate`
and w1.`pTime` <= w2.`pTime`
and w1.`pInOut` = 'Out'
set w1.`DeleteRec` = '1'

Result:

Id      empID    pType        pDate       pTime     pInOut    DeleteRec
32382   EMP-217  Attendance   2021-11-26  08:50:45  In        0
33540   EMP-217  Attendance   2021-11-26  12:34:21  Out       1
33171   EMP-217  Attendance   2021-11-26  17:13:49  Out       0
33170   EMP-217  Attendance   2021-11-26  17:09:49  Out       1
33168   EMP-217  Attendance   2021-11-26  17:11:18  In        0
33036   EMP-217  Attendance   2021-11-26  12:34:32  Out       0

Intended Output:
Id      empID    pType        pDate       pTime     pInOut    DeleteRec
32382   EMP-217  Attendance   2021-11-26  08:50:45  In        0
33540   EMP-217  Attendance   2021-11-26  12:34:21  Out       1
33171   EMP-217  Attendance   2021-11-26  17:13:49  Out       0
33170   EMP-217  Attendance   2021-11-26  17:09:49  Out       1
33168   EMP-217  Attendance   2021-11-26  17:11:18  In        0
33036   EMP-217  Attendance   2021-11-26  ***12:34:32  Out       1***

Can anyone please help?

CodePudding user response：

Why you decide that the row id=33036 should be updated?

See fiddle. None row with w1.id=33036 have all 5 criteria equal to TRUE (see criteria_1 and criteria_4, at least one of them is FALSE).