How to generate unique and sequential Id values 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?
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.)