If an access is repeated by the same user within one hour only the first is to be considered valid using MySQL

Databases
1

Hello there,

I need update the existing value on column valid_access on the table dotable using a database MySql version 5.7

+----+------------+----------+-----------+--------------+
| id | yyyy_mm_dd | hh_mm_ss | user_code | valid_access |
+----+------------+----------+-----------+--------------+
|  1 | 2021-08-02 | 00:39:31 | D29942    | NULL         |
|  2 | 2021-08-02 | 00:39:31 | D29942    | NULL         |
|  3 | 2021-08-02 | 01:07:36 | D29942    | NULL         |
|  4 | 2021-08-02 | 01:07:36 | D29942    | NULL         |
|  5 | 2021-08-02 | 01:23:33 | D29942    | NULL         |
|  6 | 2021-08-02 | 01:23:33 | D29942    | NULL         |
|  7 | 2021-08-02 | 02:44:00 | D29942    | NULL         |
+----+------------+----------+-----------+--------------+

I need this output

+----+------------+----------+-----------+--------------+
| id | yyyy_mm_dd | hh_mm_ss | user_code | valid_access |
+----+------------+----------+-----------+--------------+
|  1 | 2021-08-02 | 00:39:31 | D29942    | 1            |
|  2 | 2021-08-02 | 00:39:31 | D29942    | NULL         |
|  3 | 2021-08-02 | 01:07:36 | D29942    | NULL         |
|  4 | 2021-08-02 | 01:07:36 | D29942    | NULL         |
|  5 | 2021-08-02 | 01:23:33 | D29942    | NULL         |
|  6 | 2021-08-02 | 01:23:33 | D29942    | NULL         |
|  7 | 2021-08-02 | 02:44:00 | D29942    | 1            |
+----+------------+----------+-----------+--------------+

Because if an access is repeated by the same user within one hour only the first is to be considered valid.

Any suggestion?

My table structure and the update query below on db-fiddle.com , which offers MySQL 5.7

2

here you go –

UPDATE dotable t1
LEFT OUTER
  JOIN dotable t2
    ON t2.yyyy_mm_dd = t1.yyyy_mm_dd
   AND t2.user_code = t1.user_code
   AND t2.hh_mm_ss < t1.hh_mm_ss
   AND TIMEDIFF( t1.hh_mm_ss, t2.hh_mm_ss )
       BETWEEN '00:00:00' AND '01:00:00'
   SET t1.valid_access = 1
 WHERE t2.id IS NULL

you’ll notice that id 2 was also updated, along with id 1

that’s because they’re both “first” as specified by you

2 Likes
3

Many thanks for help.

But using your suggestion the output is:

+----+------------+----------+-----------+--------------+
| id  | yyyy_mm_dd | hh_mm_ss | user_code | valid_access |
| --- | ---------- | -------- | --------- | ------------ |
| 1   | 2021-08-02 | 00:39:31 | D29942    | 1            |
| 2   | 2021-08-02 | 00:39:31 | D29942    | 1            |
| 3   | 2021-08-02 | 01:07:36 | D29942    | NULL         |
| 4   | 2021-08-02 | 01:07:36 | D29942    | NULL         |
| 5   | 2021-08-02 | 01:23:33 | D29942    | NULL         |
| 6   | 2021-08-02 | 01:23:33 | D29942    | NULL         |
| 7   | 2021-08-02 | 02:44:00 | D29942    | 1            |
+-----+------------+----------+-----------+--------------+

Instead of

+----+------------+----------+-----------+--------------+
| id | yyyy_mm_dd | hh_mm_ss | user_code | valid_access |
+----+------------+----------+-----------+--------------+
|  1 | 2021-08-02 | 00:39:31 | D29942    | 1            |
|  2 | 2021-08-02 | 00:39:31 | D29942    | NULL         |
|  3 | 2021-08-02 | 01:07:36 | D29942    | NULL         |
|  4 | 2021-08-02 | 01:07:36 | D29942    | NULL         |
|  5 | 2021-08-02 | 01:23:33 | D29942    | NULL         |
|  6 | 2021-08-02 | 01:23:33 | D29942    | NULL         |
|  7 | 2021-08-02 | 02:44:00 | D29942    | 1            |
+----+------------+----------+-----------+--------------+
4

yes… and i explained why

1 Like
5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.