Search code examples
mysqlinnodbsql-insert

Reset auto_increment in insert into statement

I Have a table Invoice with: id (auto_increment), YEAR, invoiceNum, date.

I'm working on a Insert statement.

When the YEAR change, NumInvoice has to reset.Something like check the previous year and the current, and if they are different reset it.

Until now I have:

insert into invoice values (null, YEAR(CURDATE()),CONCAT(YEAR(CURDATE()),LAST_INSERT_ID()+1),CURRENT_TIMESTAMP());

There is a way to do this NumInvoice reset on the same insert statement?

Thanks in advance.

Solution

  • I got some solution without stored routine.

    INSERT INTO invoice (year,number,date)  SELECT  YEAR(CURDATE()),COUNT(*) + 1 ,CURDATE() FROM invoice WHERE year = YEAR(CURDATE());

    Any answer to do that with stored routines?

    Regards