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

Insert array to mysql, but add other mysql-based value to each new row before insert

I have a table 'images' like this:

ProductId SortOrder Source
1 1 source.com/234/
1 2 source.com/675/
2 1 source.com/7698/
2 2 source.com/678/
2 3 source.com/7456/

In one mysql query, I want to insert multiple rows. But, most importantly, I want to populate the next SortOrder for each row(starting at the last one for that ProductId).

For example, I would love to be able to do:

const values = [
    [2,'source.com/456546', @max_sort_order := @max_sort_order + 1],
    [2,'source.com/237675', @max_sort_order := @max_sort_order + 1]
]

const query = "SET @max_sort_order := (SELECT COALESCE(MAX( SortOrder ), 1) FROM images i WHERE ProductId = 2);INSERT INTO images (ProductId, Source, SortOrder) VALUES ?"

connection.query({sql: query, timeout: 40000, values: [values]...

Which would ideally result in the following new rows:

ProductId SortOrder Source
2 4 source.com/456/
2 5 source.com/275/

But I can’t put that @max_sort_order variable in the values, due to it being prepared values(I think).

Any idea on how to be go about this?

Tags:

2

Answers


  1. 0

    You’ll have to do the logic in JavaScript, not MySQL.

    You can get MAX(SortOrder) for all the productIDs in one call. Then you can fill in the incrementing SortOrder fields in the values array, and do a bulk insert for all of them.

    const values = [
    [2,'source.com/456546', 0],
    [2,'source.com/237675', 0]
];

// Create object whose keys are the unique ProductID values.
const pidMap = Object.fromEntries(values.map(([pid]) => [pid, 0]));
const unique_pids = Object.keys(pidMap);
const placeholders = unique_pids.map(_ => '?').join(',');
const max_query = `SELECT ProductID, MAX(SortOrder) FROM images WHERE ProductID IN (${placeholders}) GROUP BY ProductID`;
connection.query(max_query, unique_pids, function(err, result) {
    if (error) {
        throw error;
    }
    // Fill in the values of the object
    result.foreach(([pid, max]) => pidMap[pid] = max);
    // Update the SortOrder fields in the values array with incrementing numbers
    values.forEach(val => val[2] = pidMap[++val[0]]);
    connection.query('INSERT INTO images (ProductID, Source, SortOrder) VALUES ?', values, function(err, result) { ... });
});
    Login or Signup to reply.
  2. 0

    The simplest solution is secondary autoincremented column in MyISAM table:

    CREATE TABLE images (
    ProductId INT,
    SortOrder INT AUTO_INCREMENT,
    Source VARCHAR(160),
    PRIMARY KEY (ProductId, SortOrder)
    ) ENGINE = MyISAM; 
INSERT INTO images VALUES 
(1, 1,  'source.com/234/'),
(1, 2,  'source.com/675/'),
(2, 1,  'source.com/7698/'),
(2, 2,  'source.com/678/'),
(2, 3,  'source.com/7456/');
SELECT * FROM images;
    ProductId SortOrder Source
    1 1 source.com/234/
    1 2 source.com/675/
    2 1 source.com/7698/
    2 2 source.com/678/
    2 3 source.com/7456/ 
    INSERT INTO images (ProductId, Source) VALUES
(2,'source.com/456546'),
(2,'source.com/237675');
SELECT * FROM images;
    ProductId SortOrder Source
    1 1 source.com/234/
    1 2 source.com/675/
    2 1 source.com/7698/
    2 2 source.com/678/
    2 3 source.com/7456/
    2 4 source.com/456546
    2 5 source.com/237675

    fiddle

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

Back To Top
Search