根据日期从另一个表更新mysql一个表 - Update mysql one table from another table based on dates

Update mysql one table from another table based on dates

我有一个学生考勤系统的程序，并在mysql表上记录保存，如下所示，如果有，则为1，如果没有，则为0

表->考勤

uid  date       status  App
1   01/07/2013  1   
1   01/07/2013  1   
1   01/07/2013  1   
1   01/07/2013  0   
1   01/07/2013  0   
1   02/07/2013  1   
1   02/07/2013  0   
1   02/07/2013  1   
1   02/07/2013  1   
1   02/07/2013  1   
1   03/07/2013  0   
1   03/07/2013  0   
1   03/07/2013  1   
1   03/07/2013  1   
1   03/07/2013  1   
1   04/07/2013  0   
1   04/07/2013  1   
1   04/07/2013  1   
1   04/07/2013  1   
1   04/07/2013  1

我还有一张表格，学生们可以在这里提交他们的休假申请，比如下面的

表->应用

 id  uid    from            to           status
 1   1    04/07/2013    07/07/2013  approved
 2   1    11/07/2013    12/07/2013  rejected

我希望，如果状态设置为已批准，那么将在"考勤"表中搜索申请中给出的日期范围（7月4日至7月7日），无论找到的日期是0，它都会在"应用程序"列中添加1，并忽略未参加考勤的日期。

UPDATE attendance  d
  JOIN application p
    ON d.uid = p.uid
   AND d.date BETWEEN p.from AND p.to
   AND p.status = 'approved'
SET    d.app = 1
WHERE  d.status = 0

UPDATE attendance  d
  JOIN application p
    ON d.uid = p.uid
   AND d.date BETWEEN p.from AND p.to
SET    d.status = 1
WHERE  p.status = 'approved'

这应该可以实现您想要的：

UPDATE attendance a
    Set a.app=1
    WHERE a.uid IN
        (select z.uid
            FROM application z
            WHERE z.status='approved'
                AND a.date BETWEEN z.from and z.to);