Mysql – How to generate unique and sequential Id values

database-designMySQLmysql-8.0

We have an entity called Task which has properties like id, vendor id, name. So I created the following table to represent it:

CREATE TABLE `Task` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned NOT NULL,
  `name` varchar(30) NOT NULL,
  CONSTRAINT `task_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) 
  REFERENCES `Vendor` (`vendor_id`))
  ENGINE=InnoDB AUTO_INCREMENT=1 
  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

We have multiple Vendors, and we want that the id values should be sequential starting from 1 for each Vendor.

Example:

Vendor 1: ids: 1, 2, 3
Vendor 2: ids : 1, 2, 3

One of the solutions that clicks me is to, whenever a Task is added, we find out the max(id) for that vendor and then increment it by 1 and assign it to the corresponding row, e.g.:

INSERT INTO `Task` (`id`, `vendor_Id`, `name) 
VALUES ((select max(id) + 1 from Task where vendor_Id = 1), '1', 'Test');

But I get an error message with that approach:

You can't specify target table 'Task' for update in FROM clause

From MySQL docs:

You cannot update a table and select from the same table in a subquery.

Questions

How do I go about solving the problem with this approach?
Are there other options?

Best Answer

A simple rearrangement:

INSERT INTO `Task`
       (`id`, `vendor_Id`, `name`) 
    SELECT MAX(id) + 1, 123, 'Test'
        FROM Task
        WHERE vendor_Id = 123;

(I changed the vendor_id to make it clearer.)

Related Solutions

Mysql – Are two indexes needed

An index can seek by a subset of characters, as long as you're searching from the left. E.g., "Inter%" can seek, "%net" will not.

However, the first character is not necessarily the character under which the article would be sorted. "The Internet" should go under "I", not "T". You probably need two fields, DisplayTitle and SortTitle; a single-character index on the latter may be worthwhile, but most likely a full-length index will be just fine.

Indexes are typically B-trees, and a seek will jump to the right location about equally quickly whether you have 10 or 100 entries per page. Scans are another matter, but I'd start with the simplest solution and add an extra index only if performance proves inadequate in practice.

Mysql – Finding rows for a specified date range

Another way to get the result is this. It finds first all groups that the teacher has surely taught (or is going to) by checking that she has started within the month and then in another subquery it finds - for every group - the last teacher that started at the first day of the month or earlier.

With the unique index you have on the table, the second subquery should be quite efficient. The first subquery would benefit from an index on (teacherid, startdate, groupid):

SELECT groupid
FROM pupilgroupteacher
WHERE teacherid = @teacher 
  AND startdate >= @month + INTERVAL 1 DAY
  AND startdate < @month + INTERVAL 1 MONTH

UNION DISTINCT

SELECT gg.groupid
FROM 
    ( SELECT DISTINCT groupid
      FROM pupilgroupteacher
    ) AS gd
  JOIN pupilgroupteacher AS gg
    ON  gg.groupid = gd.groupid
    AND gg.startdate =
        ( SELECT MAX(gi.startdate)
          FROM pupilgroupteacher AS gi 
          WHERE gi.groupid = gd.groupid
            AND gi.startdate < @month + INTERVAL 1 DAY
        )
WHERE gg.teacherid = @teacher ;

Related Question