Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL – Using MAX or COUNT in SELECT for INSERT and Have MAX/COUNT change for each INSERT

I am using the sample Northwind DB and would like to INSERT a new product for every discontinued product and renaming the new product with an appended ‘_v2’. Problem is I would like to specify a new ProductID (for a table that would not have ProductID as autoIncrement). How can I have it get the largest ProductID number and increment it.
I tried this:

INSERT INTO `products2`( `ProductName`, `ProductID`, `SupplierID`, `CategoryID`, `QuantityPerUnit`, `UnitPrice`, `UnitsInStock`, `UnitsOnOrder`, `ReorderLevel`, `Discontinued`) 
SELECT CONCAT(p.ProductName, '_V2'), (SELECT MAX(p2.ProductID) FROM products2 p2) + p.ProductID, p.SupplierID, p.CategoryID, 30, p.UnitPrice * 2, 10, 20, 2, 'n' 
FROM products2 p 
WHERE p.Discontinued = 'y';

and problem is it seems to retrieve the the MAX productID and reusing it over and over again, giving all my new products the same MAX + 1 productID (I tested it with removing ProductID as a Primary Key) I even tried this with the same result:

INSERT INTO `products2`( `ProductName`, `ProductID`, `SupplierID`, `CategoryID`, `QuantityPerUnit`, `UnitPrice`, `UnitsInStock`, `UnitsOnOrder`, `ReorderLevel`, `Discontinued`) 
SELECT CONCAT(p.ProductName, '_V2'), (SELECT x.ProductID FROM (SELECT p.ProductID, ROW_NUMBER() OVER( ORDER BY ProductID DESC) AS rank FROM products2 p) AS x WHERE x.rank = 1) +1, p.SupplierID,p.CategoryID, 30, p.UnitPrice * 2, 10, 20, 2, 'n' 
FROM products2 p 
WHERE p.Discontinued = 'y';

I can’t get the Subquery to be re-run per Insert

Tags:

2

Answers


  1. 0

    You can tweak your query to assign the max(ProductID) in a subquery to a variable and then increment your variable within your query.

    Here is a simplified way that you can use on your problem:

    create table product (
id int,
name varchar(20),
discontinued bit(1)
);

insert into product values 
(1, 'prod1', false),
(2, 'prod2', true),
(3, 'prod3', true);

insert into product (id, name, discontinued)
select @max:=@max+1 newId, concat(p.name, '_v2') newProd, true as discontinued
  from (select @max:=max(id) from product) mx,
       product p
 where p.discontinued = 1;
 
select * from product;

    See it working here

    Login or Signup to reply.
  2. 0

    With MySQL 8 CTE and ROW_NUMBER was introduced, so use thm like

    create table product (
id int,
name varchar(20),
discontinued bit(1)
);

insert into product values (1, 'prod1', false), (2, 'prod2', true), (3, 'prod3', true);

    Records: 3  Duplicates: 0  Warnings: 0

    insert into product (id, name, discontinued)
  WITH CTE As (select max(id) as max_id from product)
  
select max_id + ROW_NUMBER() OVER(ORDER  BY id) as  newId, concat(p.name, '_v2') newProd, true as discontinued
FROM product p CROSS JOIN CTE

    Records: 3  Duplicates: 0  Warnings: 0

    SELECT * FROM product
    id name discontinued
    1 prod1 0
    2 prod2 1
    3 prod3 1
    4 prod1_v2 1
    5 prod2_v2 1
    6 prod3_v2 1

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search