0

I need to alter a database by a simple pattern. An value starting with /aa get turned into '/a/a/aa', and /ab get turned into '/a/b/ab', and /ba get turned into '/b/a/ba' and so on 0-9 and a-z. This is what I have working for the appending part

UPDATE pleasuresnginx.mag_catalog_product_entity_media_gallery tar,  (
        SELECT imgs.* FROM pleasuresnginx.mag_catalog_product_entity_media_gallery AS imgs
        WHERE (
            imgs.value LIKE '%.jpg'
            AND imgs.value LIKE '/an%'
        )
    ) src
SET tar.value =  CONCAT('/a/n',src.value)
WHERE tar.value_id = src.value_id


UPDATE pleasuresnginx.mag_catalog_product_entity_varchar tar,  (
        SELECT imgs.* FROM pleasuresnginx.mag_catalog_product_entity_varchar AS imgs
        WHERE (
            imgs.value LIKE '%.jpg'
            AND imgs.value LIKE '/an%'
        )
    ) src
SET tar.value =  CONCAT('/a/n',src.value)
WHERE tar.value_id = src.value_id

but to make it work I need to run them one at a time changing the values. What I want to do is create an array of 0-9 and a-z then loop over that and put the values in. Any ideas on how I can make this happen from with in MySQL? I can only use SQL at this time.

This is my current attempt at it

SET @myArray = '0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,';
WHILE (LOCATE(',', @myArray) > 0) DO

    SET @mainvalue = ELT(1, @myArray);
    SET @mainvalue = SUBSTRING(@myArray, LOCATE(',',@myArray) + 1);

    WHILE (LOCATE(',', @myArray) > 0) DO

        SET @subvalue = ELT(1, @myArray);
        SET @subvalue = SUBSTRING(@myArray, LOCATE(',',@myArray) + 1);

            UPDATE pleasuresnginx.mag_catalog_product_entity_varchar tar,  (
                    SELECT imgs.* FROM pleasuresnginx.mag_catalog_product_entity_varchar AS imgs
                    WHERE (
                        imgs.value LIKE '%.jpg'
                        AND imgs.value LIKE CONCAT('/',@mainvalue,'/',@subvalue,'%')
                    )
                ) src
            SET tar.value =  CONCAT('/',@mainvalue,'/',@subvalue,src.value)
            WHERE tar.value_id = src.value_id;

    END WHILE;

END WHILE;

I seem to have some syntax error still

UPDATE 1

I have moved a little closer to the right thing I think. This loops with in it's self and uses the seek of LOCATE() in a WHILE loop to move through a csv string. It seems to lock up.

DROP PROCEDURE IF EXISTS `foo_update_routine`;

DELIMITER $$

CREATE PROCEDURE `foo_update_routine`()
BEGIN
    SET @myArray = '0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z';

    WHILE (LOCATE(',', @myArray) > 0) do    

        SET @mainvalue = ELT(1, @myArray);
        SET @mainvalue = SUBSTRING(@myArray, LOCATE(',',@myArray) + 1);
        SET @subArray = '0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z';

        WHILE (LOCATE(',', @subArray) > 0) do

            SET @subvalue = ELT(1, @subArray);
            SET @subvalue = SUBSTRING(@subArray, LOCATE(',',@subArray) + 1);

            UPDATE wsumage_networks.catalog_product_entity_varchar tar,  (
                    SELECT imgs.* FROM wsumage_networks.catalog_product_entity_varchar AS imgs
                    WHERE (
                        imgs.value LIKE '%.jpg'
                        AND imgs.value LIKE CONCAT('/',@mainvalue,'/',@subvalue,'%')
                    )
                ) src
            SET tar.value =  CONCAT('/',@mainvalue,'/',@subvalue,src.value)
            WHERE tar.value_id = src.value_id;

        END WHILE;
    END WHILE;
END $$
CALL `foo_update_routine`;

UPDATE 2

This is a very nice path I think from @ypercube, But I get Error Code: 1271. Illegal mix of collations for operation 'concat' after I clear the other errors. This is what I ran.

CREATE TABLE pattern_characters
( c CHAR(1) NOT NULL PRIMARY KEY ) ;

INSERT INTO pattern_characters
  (c)
VALUES
  ('0'), ('1'), ('2'), ('3'), ('4'), 
  ('5'), ('6'), ('7'), ('8'), ('9'), 
  ('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('g'), ('h'), 
  ('i'), ('j'), ('k'), ('l'), ('m'), ('n'), ('o'), ('p'), 
  ('q'), ('r'), ('s'), ('t'), ('u'), ('v'), ('w'), ('x'), ('y'), ('z') ;

CREATE TABLE patterns
( test_pattern VARCHAR(20) NOT NULL PRIMARY KEY,
   add_prefix CHAR(4) NOT NULL
);

INSERT INTO patterns
  (test_pattern, add_prefix)
SELECT
    CONCAT(d, first.c, second.c, suffix),
    CONCAT(d, first.c, d, second,c)
FROM
    ( SELECT '/' AS d, 
             '%.jpg' AS suffix
    ) AS delimiters
  CROSS JOIN 
    pattern_characters AS first
  CROSS JOIN
    pattern_characters AS second;


UPDATE wsumage_networks.catalog_product_entity_varchar AS imgs
  JOIN patterns AS pat
    ON imgs.value LIKE pat.test_pattern
SET 
    imgs.value =  CONCAT(add_prefix, imgs.value) ;

DROP TABLE IF EXISTS pattern_characters;
DROP TABLE IF EXISTS patterns;

TABLE CREATE

just incase it make it easier

CREATE TABLE `catalog_product_entity_varchar` (
  `value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
  `entity_type_id` int(10) unsigned NOT NULL COMMENT 'Entity Type ID',
  `attribute_id` smallint(5) unsigned NOT NULL COMMENT 'Attribute ID',
  `store_id` smallint(5) unsigned NOT NULL COMMENT 'Store ID',
  `entity_id` int(10) unsigned NOT NULL COMMENT 'Entity ID',
  `value` varchar(255) DEFAULT NULL COMMENT 'Value',
  PRIMARY KEY (`value_id`),
  UNIQUE KEY `UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID` (`entity_id`,`attribute_id`,`store_id`),
  KEY `IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ATTRIBUTE_ID` (`attribute_id`),
  KEY `IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID` (`store_id`),
  KEY `IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID` (`entity_id`),
  CONSTRAINT `FK_CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_ENTT_VCHR_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_ENTT_VCHR_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4724 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Varchar Attribute Backend Table';

Final

This has all the fixes to make it run, and it ran well. Two tests and I get the correct edits to the table.

DROP TABLE IF EXISTS pattern_characters;
DROP TABLE IF EXISTS patterns;

CREATE TABLE pattern_characters
( c CHAR(1) NOT NULL PRIMARY KEY ) ;

INSERT INTO pattern_characters
  (c)
VALUES
  ('0'), ('1'), ('2'), ('3'), ('4'), 
  ('5'), ('6'), ('7'), ('8'), ('9'), 
  ('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('g'), ('h'), 
  ('i'), ('j'), ('k'), ('l'), ('m'), ('n'), ('o'), ('p'), 
  ('q'), ('r'), ('s'), ('t'), ('u'), ('v'), ('w'), ('x'), ('y'), ('z') ;

CREATE TABLE patterns
( 
    test_pattern VARCHAR(20) NOT NULL PRIMARY KEY, 
    add_prefix CHAR(4) NOT NULL
) ;

INSERT INTO patterns
  (test_pattern, add_prefix)
SELECT
    CONCAT('/', first.c, second.c, '%.jpg'),
    CONCAT('/', first.c, '/', second.c)
FROM
    ( SELECT '/' AS d,  '%.jpg' AS suffix ) AS delimiters
  CROSS JOIN 
    pattern_characters AS first
  CROSS JOIN
    pattern_characters AS second ;


UPDATE wsumage_networks.catalog_product_entity_varchar AS imgs
  JOIN patterns AS pat
    ON imgs.value LIKE pat.test_pattern
SET 
    imgs.value =  CONCAT(add_prefix, imgs.value) ;

DROP TABLE IF EXISTS pattern_characters;
DROP TABLE IF EXISTS patterns;
0

You can do it with a single UPDATE statement. We'll need a small table first:

CREATE TABLE pattern_characters
( c CHAR(1) NOT NULL PRIMARY KEY,
) ;

INSERT INTO pattern_characters
  (c)
VALUES
  ('0'), ('1'), ('2'), ('3'), ('4'), 
  ('5'), ('6'), ('7'), ('8'), ('9'), 
  ('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('g'), ('h'), 
  ('i'), ('j'), ('k'), ('l'), ('m'), ('n'), ('o'), ('p'), 
  ('q'), ('r'), ('s'), ('t'), ('u'), ('v'), ('w'), ('x'), ('y'), ('z') ;

And another one to hold our patterns. We don't really need this one (it could be created on the fly but there is no harm having it made and pre-filled with the patterns, It's only 36^2 rows anyway and the procedure more clear I think:

CREATE TABLE patterns
( test_pattern VARCHAR(20) NOT NULL PRIMARY KEY,
, add_prefix CHAR(4) NOT NULL
) ;

INSERT INTO patterns
  (test_pattern, add_prefix)
SELECT
    CONCAT(d, first.c, second.c, suffix),
    CONCAT(d, first.c, d, second,c)
FROM
    ( SELECT '/' AS d, 
             '%.jpg' AS suffix
    ) AS delimiters
  CROSS JOIN 
    pattern_characters AS first
  CROSS JOIN
    pattern_characters AS second ;

Then we can do the update in a single statement:

UPDATE pleasuresnginx.mag_catalog_product_entity_varchar AS imgs
  JOIN patterns AS pat
    ON imgs.value LIKE pat.test_pattern
SET 
    imgs.value =  CONCAT(add_prefix, imgs.value) ;
  • I like this, didn't work right off and I have updated the question. Thank you – jeremy.bass Sep 7 '14 at 7:14
  • I was able to get around the issue. I noted it above. really like this approach! – jeremy.bass Sep 7 '14 at 7:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.