MySQL 5.7
Consider the following sample data:
CREATE TABLE catalog_product_entity_media_gallery ( `value` VARCHAR(24), `entity_id` INTEGER ); INSERT INTO catalog_product_entity_media_gallery (`value`, `entity_id`) VALUES ('a01-some-item-p1-png.png', '1'), ('a01-some-item-p2-png.png', '1'), ('a01-some-item-d1-png.png', '1'), ('a01-some-item-d5-png.png', '1'), ('another-transparent.png', '2'), ('another-back.png', '2'), ('another-front.png', '2'), ('another-side.png', '2'); CREATE TABLE catalog_product_entity ( `entity_id` INTEGER, `sku` VARCHAR(3) ); INSERT INTO catalog_product_entity (`entity_id`, `sku`) VALUES ('1', 'a01'), ('2', 'b22'); CREATE TABLE catalog_product_entity_varchar ( `attribute_id` INTEGER, `value` VARCHAR(24) ); INSERT INTO catalog_product_entity_varchar (`attribute_id`, `value`) VALUES ('85', 'a01-some-item-p1-png.png'), ('85', 'another-transparent.png');
DB Fiddle of same: https://www.db-fiddle.com/f/7fAx1waY3TwjR34PanBkkv/0
With the query below, I get the following result:
select a.value as 'original_file_name', b.sku, if(isnull(c.attribute_id), 0, 1) as 'is_default', concat(sku, '_', if(isnull(c.attribute_id), concat('slideshow_', 'x'), 'default_1'), '.', substring_index(a.value, "." , -1)) as 'new_file_name' from catalog_product_entity_media_gallery a join catalog_product_entity b on b.entity_id = a.entity_id left join catalog_product_entity_varchar c on c.attribute_id = 85 and c.value = a.value order by sku, is_default desc;
+--------------------------+-----+------------+---------------------+ | original_file_name | sku | is_default | new_file_name | +--------------------------+-----+------------+---------------------+ | a01-some-item-p1-png.png | a01 | 1 | a01_default_1.png | | a01-some-item-p2-png.png | a01 | 0 | a01_slideshow_x.png | | a01-some-item-d1-png.png | a01 | 0 | a01_slideshow_x.png | | a01-some-item-d5-png.png | a01 | 0 | a01_slideshow_x.png | | another-transparent.png | b22 | 1 | b22_default_1.png | | another-back.png | b22 | 0 | b22_slideshow_x.png | | another-front.png | b22 | 0 | b22_slideshow_x.png | | another-side.png | b22 | 0 | b22_slideshow_x.png | +--------------------------+-----+------------+---------------------+
In the new_file_name
column, I want to insert an ordinal number in place of x. It should start at 1 for every new sku.
Wanted result:
a01_default_1.png a01_slideshow_1.png a01_slideshow_2.png a01_slideshow_3.png b22_default_1.png b22_slideshow_1.png b22_slideshow_2.png b22_slideshow_3.png
Advertisement
Answer
Using a variable that holds a number that increments for every row.
When is_default = 1
, the number gets reset.
drop temporary table if exists pictures; create temporary table pictures select a.entity_id, a.value as original_file_name, b.sku as sku, if(isnull(d.attribute_id), 0, 1) as is_default, substring_index(a.value, "." , -1) as file_extension from catalog_product_entity_media_gallery a join catalog_product_entity b on b.entity_id = a.entity_id left join catalog_product_entity_varchar d on d.attribute_id = 85 and d.value = a.value order by sku, is_default desc; set @number = 0; select original_file_name, sku, is_default, case when is_default = 0 then @number := @number + 1 else @number := 0 end as number, concat(sku, '_', if(is_default, 'default_1', concat('slideshow_', @number)), '.', file_extension) as new_file_name from pictures;