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?

2

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.)

0

The simplest solution is to alter the table engine to MyISAM and use (vendor_id, id) as a composite primary key. If engine altering is not safe the best solution is not to use auto_increment but simple INT updated from the trigger. PS. The table structure do not contain PK at all... - akina

Use either a trigger or stored procedure. And add a PK. - topshot

0

You can do it like this:

INSERT INTO `Task` 
    (`id`, `vendor_Id`, `name) 
VALUES 
    (null, '1', 'Test');

If a column has AUTO INCREMENT and you insert null into that field, MySQL will automatically insert the next higher number. Here is an example:

CREATE TABLE `t2` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `k1` VARCHAR(50) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)
ENGINE=MyISAM
;

then run this:

insert into t2
(id,k1)
values
(null, 'test'),
(null, 'test2');

You should see:

1   test
2   test2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.