2

I want to reset number daily,

Table : 

CREATE TABLE tickets(
    ticketId varchar(10),
    moviesname text,
    date DATE,
    seatnumber INT(5),
    PRIMARY KEY(ticketId));

is there anything SQL Syntax to reset the id daily ?

Shadow
  • 33,525
  • 10
  • 51
  • 64
leo leone
  • 65
  • 6
  • So you want to reseed an auto increment field like in the following? http://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql – xQbert Feb 23 '16 at 14:33
  • but, how about the id isnt a primary and not on auto increment mode ? – leo leone Feb 23 '16 at 14:37
  • 1
    Then how do you determine at the moment what the value of id should be? – Shadow Feb 23 '16 at 14:42
  • Then what is there to "reset"? it would likely help to illustrate by example. Table with fields, sample data, and expected results. As it currently sounds, some query is doing a "max+1" type logic and you want to constrain it by day. If you add a truncated date (no timestamp) as additional filter to your max+1 then it would reset daily. If this is what you're after be careful because max+1 when max is null gives null! – xQbert Feb 23 '16 at 14:43
  • +a coalesce() or ifnull() ;) – Shadow Feb 23 '16 at 14:45
  • i just edited the question with example table. i just want to manually add the number with SQL syntax (with MAX) and reseed it daily back to 1. – leo leone Feb 23 '16 at 14:54
  • 1. Why would you do that? 2. You would have to change your pk to be ticketId and date, otherwise you would get duplicate pk error messages. 3. You will have concurrency issues with such solutions. 4. Using myisam and auto_increment you could do this without any coding. – Shadow Feb 23 '16 at 14:59
  • i use it on polyclinic apps that use queue number for entry system – leo leone Feb 23 '16 at 15:04

1 Answers1

1

I see 2 possible approaches:

1. Use myisam table's special auto_increment feature:

For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix.

  • Make sure that tickets table is myisam.
  • tickeid column must be auto_increment
  • change the primary key to be date, tickeid. The order of the columns is paramount!!!!

2. Spice-up your current max() query to determine the next value

  • Change the primary key to be date, tickeid. Order of the fields is not important. You cannot leave ticketid to be the pk alone, since you want to restart the numbering daily, which would lead to duplicate index value error message.

  • Use the following query to get the next ticketid:

    select coalesce(max(ticketid),1) from tickets where date=curdate()

The where criteria ensures that every day the ticketid values would restart. The coalesce() is there to assign the number 1 as the first id every day, since max() returns null if there are no records to satisfy the where criteria.

However, the risk with this solution is that multiple statements executed at the same time may yield the same id. Either you handle this situation with proper error handling within your application code (e.g. attempt to insert again), or you serialise the inserts into the tickets table by using explicit locks.

Shadow
  • 33,525
  • 10
  • 51
  • 64