CODEWITHSUNDEEP
mysqldatesql-update
Antonio Mailtraq
Antonio Mailtraq

Reputation: 1407

Update rows in MySQL for interval hour

I need update in Mysql table tbl_XX60 the fields Valid and ccp and I have tried this query :

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');

I have applied three conditions in where clause query: 

 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')

without success because in the table tbl_XX60 I find as updated these rows that not contained the interval of hour selected >= '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 |
+--------------+------------+----------+-----+-------+

Please help me, thank you in advance.

#Edit 1

I have used AND instead that OR, but the return is not correct, the first four records are not updated because are > 21 hour.

+--------------+------------+----------+-----+-------+
| 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

Upvotes: 1

Views: 84

Answers (1)

genespos
genespos

Reputation: 3311

Have you tryed using AND instead of 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');

If you need interval from 09:00:00 to 21:59:59 you need to use

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

Upvotes: 1

Related Questions