Update column based on min number with status column in mysql?

Question

I want to update number 2 with status = 8

UPDATE tqueue 
SET status = '8' 
WHERE (SELECT MIN(number) 
       FROM tqueue
       WHERE STATUS IN ('4')
         AND get_ticket >= CURDATE( ) 
         AND get_ticket < DATE_ADD( CURDATE(), INTERVAL 1 DAY) 
       GROUP BY service)

Output:

--------------------------------------------------
number   |    status   |  get_ticket 
--------------------------------------------------
   3            4         2017-02-18  13:43:01
   2            4         2017-02-18  12:34:03
   1            8         2017-02-18  10:04:59

`update tqueue set status = 8 where number = 2`. Please explain if this solves your problem or why not — Gordon Linoff, Feb 18 '17 at 17:08
update your question in a consistent wya .. you refer to column and condition that are not in you sample and in your explanation .. — ScaisEdge, Feb 18 '17 at 17:09
when every time i update the data min number with status 4 will change to status 8 not only number 2 — budi, Feb 18 '17 at 17:10

score 0 · Accepted Answer · edited May 23 '17 at 12:09

0

Are you looking for updating min number in status=8 into status=4 ?

Try this one:

 SELECT @mynumber:=number FROM tqueue
 WHERE STATUS = '4'
 AND get_ticket >= CURDATE( )
 AND get_ticket < DATE_ADD( CURDATE() , INTERVAL 1 DAY )
 GROUP BY service
 order by number asc
 limit 0,1 ;

 UPDATE tqueue SET status = '8' where number = @mynumber ;

Also, you can use temporary table for update self-reference:

 CREATE TEMPORARY TABLE mytbl_numbers AS 
 SELECT number FROM tqueue
 WHERE STATUS = '4'
 AND get_ticket >= CURDATE( )
 AND get_ticket < DATE_ADD( CURDATE() , INTERVAL 1 DAY )
 GROUP BY service
 order by number asc
 limit 0,1 ;

 UPDATE tqueue SET status = '8' where number in (SELECT * FROM  mytbl_numbers) ;

More useful Answers may be found at [+] & [+] & ...

edited May 23 '17 at 12:09

Community

1
1

answered Feb 18 '17 at 17:22

MohaMad

2,575
2
14
26

1

it's error #1093 - You can't specify target table 'tqueue' for update in FROM clause – budi Feb 18 '17 at 17:41
Sorry for my mistake, we cannot use one table for target and source table in `update` and `delete`. So I changed code, does it solves? – MohaMad Feb 18 '17 at 19:43

Update column based on min number with status column in mysql?

1 Answers1