71GA 71GA - 1 month ago 9
MySQL Question

How to INSERT a value based on the current date and a generated sequence number in MySQL?

I have this MySQL table:

CREATE TABLE bills
(
id_interess INT UNSIGNED NOT NULL,
id_bill VARCHAR(30) NULL,
PRIMARY KEY (id_interess)
) ENGINE=InnoDB;


And now I want to be able to manually insert unique integer for
id_interess
and automatically generate
id_bill
so that it consists of a current date and an integer (integer resets on a new year using trigger) like this:

id_interess |id_bill |
------------+-----------+
1 |20170912-1 |
2 |20171030-2 |
6 |20171125-3 |
10 |20171231-4 |
200 |20180101-1 |
3 |20180101-2 |
8 |20180102-3 |


If anyone has direct solution to this using only one query, I would be very glad! I only came up with a solution that uses three queries, but I still get some errors...

My newbie attempt: I created an additional column
id_bill_tmp
which holds integer part of
id_bill
like this:

CREATE TABLE bill
(
id_interess INT UNSIGNED NOT NULL,
id_bill_tmp INT UNSIGNED NULL,
id_bill VARCHAR(30) NULL,
PRIMARY KEY (id_interess)
) ENGINE=InnoDB;


Table from above would in this case look like this (note that on new year
id_bill_tmp
is reset to
1
and therefore I can't use
AUTO_INCREMENT
which can only be used on keys and keys need unique values in a column):

id_interess |id_bill_tmp |id_bill |
------------+--------------+-----------+
1 |1 |20170912-1 |
2 |2 |20171030-2 |
6 |3 |20171125-3 |
10 |4 |20171231-4 |
200 |1 |20180101-1 |
3 |2 |20180101-2 |
6 |3 |20180102-3 |


So for example to insert 1st row from the above table, table would have to be empty, and I would insert a value in three queries like this:

1st query:

INSERT INTO racuni (id_interess) VALUES (1);


I do this first because I don't know how to increment a nonexistent value for
id_bill_tmp
and this helped me to first get
id_bill_tmp = NULL
:

id_interess |id_bill_tmp |id_bill |
------------+--------------+-----------+
1 |[NULL] |[NULL] |


2nd query

Now I try to increment
id_bill_tmp
to become
1
- I tried two queries both fail saying:


table is specified twice both as a target for 'update' and as a separate source for data


This are the queries I tried:

UPDATE bills
SET id_bill_tmp = (SELECT IFNULL(id_bill_tmp, 0)+1 AS id_bill_tmp FROM bills)
WHERE id_interess = 1;

UPDATE bills
SET id_bill_tmp = (SELECT max(id_bill_tmp)+1 FROM bills)
WHERE id_interess = 1;


3rd query:

The final step would be to reuse
id_bill_tmp
as integer part of
id_bill
like this:

UPDATE bills
SET id_bill = concat(curdate()+0,'-',id_bill_tmp)
WHERE id_interess = 1;


so that I finally get

id_interess |id_bill_tmp |id_bill |
------------+--------------+-----------+
1 |1 |20170912-1 |


So if anyone can help me with the 2nd query or even present a solution with a single query or even without using column
id_bill_tmp
it would be wonderful.

Answer Source

Solution #1 - with the extra column

Demo

http://rextester.com/MXJI45758

SQL

INSERT INTO bills (id_interess, id_bill_tmp, id_bill)
VALUES (
    1, -- (Change this value appropriately for each insert)
    IF(LEFT((SELECT id_bill FROM (SELECT MAX(id_bill) AS id_bill FROM bills) b1), 4)
       = DATE_FORMAT(CURDATE(),'%Y'), 
       IFNULL(
           (SELECT id_bill_tmp
            FROM (SELECT id_bill_tmp
                  FROM bills
                  WHERE id_bill = (SELECT MAX(id_bill) FROM bills)) b2),
           0),
       0)
       + 1,
    CONCAT(DATE_FORMAT(CURDATE(),'%Y%m%d'), '-' , id_bill_tmp));

Notes

The query looks slightly more complicated that it actually is because of the issue that MySQL won't let you directly use a subselect from the same table that's being inserted into. This is circumvented using the method of wrapping another subselect around it as described here. It also relies on the convenient fact that the values in the id_bill column are naturally ordered such that selecting the maximum value in this column will return the latest one.

Solution #2 - without the extra column

Demo

http://rextester.com/JPA12600

SQL

INSERT INTO bills (id_interess, id_bill) VALUES (
   1, -- (Change this value appropriately for each insert)
   CONCAT(DATE_FORMAT(CURDATE(),'%Y%m%d'),
          '-' ,
          IF(LEFT((SELECT id_bill FROM (SELECT MAX(id_bill) AS id_bill FROM bills) b1), 4) 
             = DATE_FORMAT(CURDATE(),'%Y'), 
              IFNULL(
                  (SELECT id_bill_tmp
                   FROM (SELECT SUBSTR(MAX(id_bill), 10) AS id_bill_tmp
                         FROM bills) b2),
                  0),
              0)
              + 1));

Notes

This is along the same lines as above but gets the numeric value that would have been in id_bill_tmp by extracting from the right part of id_bill from the 10th character position onwards via SUBSTR(id_bill, 10).

Step by step breakdown

  1. CONCAT(...) assembles the string by concatenating its parts together.
  2. DATE_FORMAT(CURDATE(),'%Y%m%d') formats the current date as yyyymmdd (e.g. 20170923).
  3. The IF(..., <x>, <y>) is used to check whether the most recent date that is already present is for the current year: If it is then the numeric part should continue by incrementing the sequence, otherwise it is reset to 1.
  4. LEFT(<date>, 4) gets the year from the most recent date - by extracting from the first 4 characters of id_bill.
  5. SELECT MAX(id_bill) AS id_bill FROM bills gets the most recent date + sequence number from id_bill and gives this an alias of id_bill. (See the notes above about why this works and also about why the subquery also needs to be given an alias (b1) and then wrapped in another SELECT).
  6. DATE_FORMAT(CURDATE(),'%Y') formats the current date as a year (e.g. 2017) for the IF comparison mentioned in (3) above.
  7. IFNULL(<x>, <y>) is used for the very first row since no existing row will be found so the result will be NULL. In this case the numeric part should begin at 1.
  8. SELECT SUBSTR(MAX(id_bill), 10) AS id_bill_tmp FROM bills selects the most recent date + sequence number from id_bill and then extracts its sequence number, which is always from character position 10 onwards. Again, this subquery needs to be aliased (b2) and wrapped in another SELECT.
  9. + 1 increments the sequence number. (Note that this is always done since 0 is used in the cases described above where the sequence number should be set to 1).