Copy values via MySQL query (phpMyAdmin)
I have a Wordpress + Woocommerce site. I need to export .xml for another site.
The post_meta table has fields: _sku woocommerce_xml_disabled woocommerce_xml_EAN
I need to build this query:
if '_sku' not empty
copy value from '_sku' to 'woocommerce_xml_EAN'
if '_sku' is empty
set 'woocommerce_xml_disabled' value to '1'.
Customer used the SKU field to enter EAN codes, and some products have the same EAN. This field does not accept duplicates, so there are cases when the values look like this: "9001616391101 (1)", "9001616391101 (2)". It would be great if the query could remove these (1) numbers after copying. "woocommerce_xml_EAN" may have duplicate EANs.
This will save me a lot of work.
Here's a small SQL example:
(181195, 14947, 'cmsms_breadcrumbs', 'default'),
(181196, 14947, 'cmsms_custom_breadcrumbs', 'a:1:{i:0;a:2:{i:0;s:0:"";i:1;s:0:"";}}'),
(181198, 14947, 'woocommerce_xml_disabled', ''),
(181199, 14947, 'woocommerce_xml_alternative_name', ''),
(181200, 14947, 'woocommerce_xml_alternative_desc', ''),
(181201, 14947, 'woocommerce_xml_group', ''),
(181202, 14947, '_woocommerce_gpf_data', 'a:0:{}'),
(181217, 14946, '_thumbnail_id', '9874'),
(181218, 14946, 'total_sales', '0'),
Data of one product sample:
CREATE TABLE IF NOT EXISTS `wp_postmeta` (
`meta_id` bigint(20) unsigned NOT NULL,
`post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`meta_value` longtext COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=185004 ;
INSERT INTO `wp_postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES
(173877, 14819, '_visibility', 'visible'),
(173878, 14819, '_stock_status', 'instock'),
(173879, 14819, '_downloadable', 'no'),
(173880, 14819, '_virtual', 'no'),
(173881, 14819, '_regular_price', '13.9'),
(173882, 14819, '_sale_price', ''),
(173883, 14819, '_purchase_note', ''),
(173884, 14819, '_featured', 'no'),
(173889, 14819, '_sku', '9001616391101 (5)'),
(173893, 14819, '_price', '13.9'),
(173919, 14819, 'woocommerce_xml_disabled', '1'),
(173923, 14819, '_woocommerce_gpf_data', 'a:0:{}'),
(173938, 14819, '_thumbnail_id', '10851'),
(173939, 14819, 'total_sales', '0'),
(182118, 14819, 'woocommerce_xml_EAN', '9001616391101')
source to share
Here you have the toughest requirements, complicated by the key / value structure of the table wp_postmeta
. Let me tackle this in two steps. The first would be to update rows where the field woocommerce_xml_EAN
already exists for the corresponding value _sku
, and at the same time override the parenthesis values (1)
usingSUBSTRING_INDEX()
. This step is woocommerce_xml_disabled
also set to 1
if _sku
empty.
All of this will be accomplished by joining wp_postmeta
oneself. On the one hand, the connection is used _sku
, and on the other, the pod is used post_id
to update the fields woocommerce_xml_EAN, woocommerce_xml_disabled
.
UPDATE
wp_postmeta sku
INNER JOIN wp_postmeta ean
ON sku.post_id = ean.post_id
-- Join conditions ensure only the needed keys are modified
AND sku.meta_key = '_sku'
AND ean.meta_key IN ('woocommerce_xml_EAN', 'woocommerce_xml_disabled')
SET ean.meta_value = CASE
-- the key/value store is crazy here :-)
-- First strip the (1) from the _sku and set the
-- resultant value into meta_value for the woocommerce_xml_EAN key
WHEN ean.meta_key = 'woocommerce_xml_EAN'
THEN TRIM(SUBSTRING_INDEX(sku.meta_value, '(', 1))
-- Then set '1' for the disabled field if _sku is empty
WHEN ean.meta_key = 'woocommerce_xml_disabled' AND sku.meta_value = ''
THEN '1'
-- But make sure to use the original value if sku wasn't empty!
WHEN ean.meta_key = 'woocommerce_xml_disabled' AND sku.meta_value <> ''
THEN ean.meta_value
END
WHERE sku.meta_key = '_sku'
AND ean.meta_key IN ('woocommerce_xml_EAN', 'woocommerce_xml_disabled');
Now for the second step, as some _sku
may not have an associated woocommerce_xml_EAN
one that needs to be added using INSERT
. We'll use INSERT INTO... SELECT
using null LEFT JOIN
to find those that don't exist yet and insert them. He uses the same trick SUBSTRING_INDEX()
.
INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT
-- Insert a new row with the post_id,
sku.post_id,
-- literal string for the EAN
'woocommerce_xml_EAN',
-- And the modified _sku value
TRIM(SUBSTRING_INDEX(sku.meta_value, '(', 1))
FROM
wp_postmeta sku
LEFT JOIN wp_postmeta ean
ON sku.post_id = ean.post_id
AND sku.meta_key = '_sku'
AND ean.meta_key = 'woocommerce_xml_EAN'
WHERE
sku.meta_key = '_sku'
-- NULL on the ean side of the join means it doesn't exist already
AND ean.meta_id IS NULL;
And here's a demo to prove that it all really works. http://sqlfiddle.com/#!9/30b84/2
In the demo, I added two additional ones post_id
that had no previous meaning for the EAN, to make sure it was created for them woocommerce_xml_EAN
. Them post_id
14820, 14821
, and you'll find them at the bottom of the result set.
source to share