Heri Heri - 9 months ago 30
MySQL Question

update sql mysql based on time work but function in different day?

i had table like this when i update with my sql i had two changes in different day, i want change just in current day look what happen in the table

-------------------------------------------------------
service loket number status get_ticket
-------------------------------------------------------
1 null 6 4 2017/3/12 12:00:07
1 null 5 4 2017/3/12 11:34:09
1 null 4 4 2017/3/12 11:02:20
1 null 3 4 2017/3/12 10:42:10
1 null 2 4 2017/3/12 09:32:10
1 1 1 8 2017/3/12 09:32:10 <----when i updated my current date
1 null 6 4 2017/2/28 12:05:07
1 null 5 4 2017/2/28 11:37:09
1 null 4 4 2017/2/28 10:02:20
1 null 3 4 2017/2/28 09:42:10
1 null 2 4 2017/2/28 09:36:10
1 1 1 8 2017/32/28 08:32:10 <----this changed too but i want it not change because this coloum in different day


update tqueue set status =8,loket=1 where service=1 and
number = (SELECT number FROM (SELECT * FROM tqueue) AS something
WHERE STATUS = '4' AND get_ticket >= CURDATE( )
AND get_ticket < DATE_ADD( CURDATE( ) , INTERVAL 1
DAY ) group by service);

Answer Source

Why are you using a subquery?

update tqueue
    set status = 8,
        loket = 1
    where service = 1 and status =  4 and
          get_ticket >= curdate( )  and
          get_ticket < date_add( curdate( ) , interval 1 day)
    limit 1;