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.
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.
How do I go about solving the problem with this approach?
Are there other options?