I have a simple table named product_images defined as:
FieldType Null Key Default Extra
image_id int NO PRI
product_id int NO PRI
image_url varchar(255) NO
public_id varchar(225) YES
Whenever an image is deleted I just want to reset the image_id of same product_id of my table.
For example:I have 5 images with 1,2,3,4,5. Now after deletion of the 2nd image I am left with 1,3,4,5. So I wish to fix their numbering after deletion to 1,2,3,4.
For this,the query I have used is:
SET @row_number := 0;
UPDATE product_images
SET image_id = (@row_number := @row_number + 1)
WHERE product_id = ?
ORDER BY image_id;
And I am getting this warning :
0 row(s) affected, 1 warning(s): 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. Rows matched: 2 Changed: 0 Warnings: 1
It works fine on MySQL Workbench, but when this query is executed through my ExpressJS server which is using mysql2 package to interact with the database,I get an error:1064 (Error in SQL syntax) and the query fails to execute.
In package.json the dependency version of "mysql2" is ^3.0.1
The version of MySQL I am using is : 8.0.28
