mysql - 间隔小时更新 MySQL 中的行

Question

我需要更新 Mysql 表 tbl_XX60田野 Valid和 ccp我试过这个查询:

UPDATE `tbl_XX60` CA
JOIN `tbl_users` A ON A.UserNumber = CA.UserNumber
SET CA.Valid = 1,
 CA.ccp = 'y'
WHERE
    CA.dateReg BETWEEN DATE_SUB('2016-01-11', INTERVAL 7 DAY)
AND '2016-01-11'
AND (
    HOUR(CA.HourReg) >= '09'
    OR HOUR(CA.HourReg) <= '21'
)
AND A.PR_Cod IN ('054');

我在 where 中应用了三个条件子句查询:

 1. CA.dateReg BETWEEN DATE_SUB('2016-01-11', INTERVAL 7 DAY) AND
    '2016-01-11'
 2. HOUR(CA.HourReg) >= '09' OR HOUR(CA.HourReg) <= '21'
 3. A.PR_Cod IN ('054')

没有成功因为在表tbl_XX60我发现更新后的这些行不包含所选的小时间隔 >= '09' OR <= '21' :

+--------------+------------+----------+-----+-------+
| UserNumber   | dateReg    | HourReg  | ccp | VALID |
+--------------+------------+----------+-----+-------+
| MPQR16000509 | 2016-01-11 | 02:47:24 | y   |     1 |
| MPQR16000643 | 2016-01-11 | 21:04:01 | y   |     1 |
| MPQR16000647 | 2016-01-11 | 21:21:57 | y   |     1 |
| MPQR16000665 | 2016-01-11 | 21:31:00 | y   |     1 |
| MPQR16000649 | 2016-01-11 | 21:43:08 | y   |     1 |
| MPQR16000651 | 2016-01-11 | 22:22:13 | y   |     1 |
| MPQR16000653 | 2016-01-11 | 22:25:35 | y   |     1 |
| MPQR16000659 | 2016-01-11 | 22:38:02 | y   |     1 |
| MPQR16000661 | 2016-01-11 | 23:04:16 | y   |     1 |
+--------------+------------+----------+-----+-------+

请帮助我，在此先感谢您。

#编辑 1

我用过AND而不是 OR , 但返回不正确，前四个记录未更新，因为 > 21 小时。

+--------------+------------+----------+-----+-------+
| UserNumber   | dateReg    | HourReg  | ccp | Valid |
+--------------+------------+----------+-----+-------+
| MPQR16000649 | 2016-01-11 | 21:43:08 | y   |     1 |
| MPQR16000665 | 2016-01-11 | 21:31:00 | y   |     1 |
| MPQR16000647 | 2016-01-11 | 21:21:57 | y   |     1 |
| MPQR16000643 | 2016-01-11 | 21:04:01 | y   |     1 |
| MPQR16000629 | 2016-01-11 | 20:09:32 | y   |     1 |
| MPQR16000623 | 2016-01-11 | 20:07:07 | y   |     1 |
| MPQR16000509 | 2016-01-11 | 02:47:24 | N   |     0 |
| MPQR16000549 | 2016-01-11 | 11:29:43 | y   |     1 |
| MPQR16000551 | 2016-01-11 | 11:32:37 | y   |     1 |
| MPQR16000569 | 2016-01-11 | 12:49:02 | y   |     1 |
| MPQR16000571 | 2016-01-11 | 13:09:47 | y   |     1 |
| MPQR16000573 | 2016-01-11 | 13:26:46 | y   |     1 |
| MPQR16000585 | 2016-01-11 | 15:33:14 | y   |     1 |
| MPQR16000651 | 2016-01-11 | 22:22:13 | N   |     0 |
| MPQR16000653 | 2016-01-11 | 22:25:35 | N   |     0 |
| MPQR16000659 | 2016-01-11 | 22:38:02 | N   |     0 |
| MPQR16000661 | 2016-01-11 | 23:04:16 | N   |     0 |
+--------------+------------+----------+-----+-------+
17 rows in set

Answer 1

您是否尝试过使用 AND 而不是 OR ？

UPDATE `tbl_XX60` CA
JOIN `tbl_users` A ON A.UserNumber = CA.UserNumber
SET CA.Valid = 1,
 CA.ccp = 'y'
WHERE
    CA.dateReg BETWEEN DATE_SUB('2016-01-11', INTERVAL 7 DAY)
AND '2016-01-11'
AND (
    HOUR(CA.HourReg) >= '09'
    AND HOUR(CA.HourReg) <= '21'
)
AND A.PR_Cod IN ('054');

如果你需要从 09:00:00 到 21:59:59 的间隔，你需要使用

AND (
    HOUR(CA.HourReg) >= '09'
    AND HOUR(CA.HourReg) < '22'
)