Car Mat SQL

mats.sql

dir /b> files.txt
="UPDATE `data_templates` SET `changed` = NOW() WHERE `middleton_code` LIKE "&SUBSTITUTE(A1, ".svg", "")&";"
="UPDATE `data_templates` SET `changed` = `changed`, `enabled` = 1 WHERE `enabled` = 2 AND `middleton_code` LIKE "&SUBSTITUTE(A1, ".svg", "")&";"


UPDATE `ukvehicledata_map` SET `UvidList` = replace(`UvidList`, ' | ', '|A');
UPDATE `ukvehicledata_map` SET `UvidList` = CONCAT('|A', `UvidList`, '|');

SELECT * FROM `data_templates` WHERE `middleton_code` = 'tbc' AND `is_coming_soon` != 1;

SELECT * FROM `data_templates` WHERE `mats` IN ('1','2','3','4') AND `price_scheme` LIKE '1' AND `clips_qty` IN (null,0,1,2,3,4) AND `express_option` = 0 AND `middleton_code` != 'tbc' AND `enabled` BETWEEN 1 AND 9;


UPDATE `listing_status` SET `modified`=DATE_SUB(NOW(),INTERVAL 3 YEAR) WHERE `website` = 'https://mailordercarparts.co.uk/' AND `unique_id` != '';

SELECT * FROM `data_templates` WHERE `years` LIKE '%-present' AND `years_long` LIKE '%|2021|' AND `years_long` != '';
UPDATE `data_templates` SET `years_long` = CONCAT(`years_long`, '2022|'), `changed` = NOW() WHERE `years` LIKE '%-present' AND `years_long` LIKE '%|2021|' AND `years_long` != '';


UPDATE `product_templates_v2` SET `description` = replace(description, '{standard and velour|velour and standard} grades of carpet', '{standard, velour and luxury|luxury, velour and standard} grades of carpet');
UPDATE `product_templates_v2` SET `description` = replace(description, 'Our standard and velour mats also have a heel pad', 'Our standard, velour, and luxury mats also have a heel pad');
UPDATE `product_templates_v2` SET `description` = replace(description, 'Premium & Velour', 'Premium, Velour & Luxury');
UPDATE `product_templates_v2` SET `description` = replace(description, 'Premium and Velour', 'Premium, Velour and Luxury');
UPDATE `product_templates_v2` SET `description` = replace(description, 'Velour & Premium', 'Luxury, Velour & Premium');
UPDATE `product_templates_v2` SET `description` = replace(description, 'Velour and Premium', 'Luxury, Velour and Premium');


UPDATE `product_templates_cache_v2` SET `description` = replace(description, 'on standard and velour grades of carpet', 'on standard, velour and luxury grades of carpet');
UPDATE `product_templates_cache_v2` SET `description` = replace(description, 'on velour and standard grades of carpet', 'on luxury, velour and standard grades of carpet');
UPDATE `product_templates_cache_v2` SET `description` = replace(description, 'Our standard and velour mats also have a heel pad', 'Our standard, velour, and luxury mats also have a heel pad');
UPDATE `product_templates_cache_v2` SET `description` = replace(description, 'our Premium & Velour', 'our Premium, Velour & Luxury');
UPDATE `product_templates_cache_v2` SET `description` = replace(description, 'our Premium and Velour', 'our Premium, Velour and Luxury');
UPDATE `product_templates_cache_v2` SET `description` = replace(description, 'our Velour & Premium', 'our Luxury, Velour & Premium');
UPDATE `product_templates_cache_v2` SET `description` = replace(description, 'our Velour and Premium', 'our Luxury, Velour and Premium');
UPDATE `product_templates_cache_v2` SET `description` = replace(description, 'on Premium & Velour', 'on Premium, Velour & Luxury');
UPDATE `product_templates_cache_v2` SET `description` = replace(description, 'on Premium and Velour', 'on Premium, Velour and Luxury');
UPDATE `product_templates_cache_v2` SET `description` = replace(description, 'on Velour & Premium', 'on Luxury, Velour & Premium');
UPDATE `product_templates_cache_v2` SET `description` = replace(description, 'on Velour and Premium', 'on Luxury, Velour and Premium');



UPDATE `catalog_product_entity_text` SET `value` = replace(value, 'on standard and velour grades of carpet', 'on standard, velour and luxury grades of carpet') WHERE `attribute_id` = (SELECT `attribute_id` FROM `eav_attribute` WHERE `attribute_code` = 'description' AND `entity_type_id` = (SELECT `entity_type_id` FROM `eav_entity_type` WHERE `entity_type_code` = 'catalog_product'));
UPDATE `catalog_product_entity_text` SET `value` = replace(value, 'on velour and standard grades of carpet', 'on luxury, velour and standard grades of carpet') WHERE `attribute_id` = (SELECT `attribute_id` FROM `eav_attribute` WHERE `attribute_code` = 'description' AND `entity_type_id` = (SELECT `entity_type_id` FROM `eav_entity_type` WHERE `entity_type_code` = 'catalog_product'));
UPDATE `catalog_product_entity_text` SET `value` = replace(value, 'Our standard and velour mats also have a heel pad', 'Our standard, velour, and luxury mats also have a heel pad') WHERE `attribute_id` = (SELECT `attribute_id` FROM `eav_attribute` WHERE `attribute_code` = 'description' AND `entity_type_id` = (SELECT `entity_type_id` FROM `eav_entity_type` WHERE `entity_type_code` = 'catalog_product'));
UPDATE `catalog_product_entity_text` SET `value` = replace(value, 'our Premium & Velour', 'our Premium, Velour & Luxury') WHERE `attribute_id` = (SELECT `attribute_id` FROM `eav_attribute` WHERE `attribute_code` = 'description' AND `entity_type_id` = (SELECT `entity_type_id` FROM `eav_entity_type` WHERE `entity_type_code` = 'catalog_product'));
UPDATE `catalog_product_entity_text` SET `value` = replace(value, 'our Premium and Velour', 'our Premium, Velour and Luxury') WHERE `attribute_id` = (SELECT `attribute_id` FROM `eav_attribute` WHERE `attribute_code` = 'description' AND `entity_type_id` = (SELECT `entity_type_id` FROM `eav_entity_type` WHERE `entity_type_code` = 'catalog_product'));
UPDATE `catalog_product_entity_text` SET `value` = replace(value, 'our Velour & Premium', 'our Luxury, Velour & Premium') WHERE `attribute_id` = (SELECT `attribute_id` FROM `eav_attribute` WHERE `attribute_code` = 'description' AND `entity_type_id` = (SELECT `entity_type_id` FROM `eav_entity_type` WHERE `entity_type_code` = 'catalog_product'));
UPDATE `catalog_product_entity_text` SET `value` = replace(value, 'our Velour and Premium', 'our Luxury, Velour and Premium') WHERE `attribute_id` = (SELECT `attribute_id` FROM `eav_attribute` WHERE `attribute_code` = 'description' AND `entity_type_id` = (SELECT `entity_type_id` FROM `eav_entity_type` WHERE `entity_type_code` = 'catalog_product'));
UPDATE `catalog_product_entity_text` SET `value` = replace(value, 'on Premium & Velour', 'on Premium, Velour & Luxury') WHERE `attribute_id` = (SELECT `attribute_id` FROM `eav_attribute` WHERE `attribute_code` = 'description' AND `entity_type_id` = (SELECT `entity_type_id` FROM `eav_entity_type` WHERE `entity_type_code` = 'catalog_product'));
UPDATE `catalog_product_entity_text` SET `value` = replace(value, 'on Premium and Velour', 'on Premium, Velour and Luxury') WHERE `attribute_id` = (SELECT `attribute_id` FROM `eav_attribute` WHERE `attribute_code` = 'description' AND `entity_type_id` = (SELECT `entity_type_id` FROM `eav_entity_type` WHERE `entity_type_code` = 'catalog_product'));
UPDATE `catalog_product_entity_text` SET `value` = replace(value, 'on Velour & Premium', 'on Luxury, Velour & Premium') WHERE `attribute_id` = (SELECT `attribute_id` FROM `eav_attribute` WHERE `attribute_code` = 'description' AND `entity_type_id` = (SELECT `entity_type_id` FROM `eav_entity_type` WHERE `entity_type_code` = 'catalog_product'));
UPDATE `catalog_product_entity_text` SET `value` = replace(value, 'on Velour and Premium', 'on Luxury, Velour and Premium') WHERE `attribute_id` = (SELECT `attribute_id` FROM `eav_attribute` WHERE `attribute_code` = 'description' AND `entity_type_id` = (SELECT `entity_type_id` FROM `eav_entity_type` WHERE `entity_type_code` = 'catalog_product'));

SELECT oie.* FROM `orders_items_extra` AS oie
INNER JOIN ded_orderitem_status AS dos ON dos.ExternalOrderID = oie.purchaseOrder
WHERE dos.Status = 'SHIPPED' AND oie.status != 3;

SELECT oie.* FROM `orders_items_extra` AS oie
INNER JOIN ded_orderitem_status AS dos ON dos.ExternalOrderID = oie.purchaseOrder
WHERE oie.SkuOptions LIKE 'RubberHD%' AND dos.Status = 'SHIPPED' AND oie.status != 3;

SELECT oie.* FROM `orders_items_extra` AS oie
INNER JOIN ded_orderitem_status AS dos ON dos.ExternalOrderID = oie.purchaseOrder
WHERE oie.shippingMethod = 'APC' AND dos.Status = 'SHIPPED' AND oie.status != 3;

UPDATE `orders_items_extra` AS oie
INNER JOIN ded_orderitem_status AS dos ON dos.ExternalOrderID = oie.purchaseOrder
SET oie.status = 3
WHERE oie.SkuOptions LIKE 'RubberHD%' AND dos.Status = 'SHIPPED' AND oie.status != 3;

UPDATE `orders_items_extra` AS oie
INNER JOIN ded_orderitem_status AS dos ON dos.ExternalOrderID = oie.purchaseOrder
SET oie.status = 3
WHERE oie.shippingMethod = 'APC' AND dos.Status = 'SHIPPED' AND oie.status != 3;


SELECT * FROM `orders_updates_actions` WHERE `createdAt` <= DATE_SUB(NOW(), INTERVAL 30 DAY) AND `processedAt` IS NULL AND `updateWeb` = 1;
UPDATE `orders_updates_actions` SET `updateWeb` = 0 WHERE `createdAt` <= DATE_SUB(NOW(), INTERVAL 30 DAY) AND `processedAt` IS NULL AND `updateWeb` = 1;