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?
2
Answers
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 incrementingSortOrder
fields in thevalues
array, and do a bulk insert for all of them.The simplest solution is secondary autoincremented column in MyISAM table:
fiddle