Howto Manipulate WooCommerce Metadata via SQL

With Germanized on board, WooCommerce has several ways to save article numbers. Has the intern spent two days entering the EAN in the wrong field for the 3000 articles in the WooCommerce online shop? Then an SQL superhero has to save the day who can copy content from one field to another in no time.

Multichannel eCommerce has been coming up quite often for us lately. Here, a cloud ERP, plentymarkets for example, sits on top of our customer’s merchandise management system and distributes products, stocks and prices to sales platforms such as Amazon, ebay, Zalando, Rakuten and also our own online shop.

We currently had the requirement that our customer had maintained an EAN in the merchandise management system and this was also stored in the WooCommerce shop as a GTIN. The multi-channel service provider, in this case cloudstock, could do little with this. Their software is designed to match the stock and price of the articles via the WooCommerce article number (SKU).

Our customer now had the choice of either having the trainee write all EANs into the correct field, financing a reprogramming of cloudstock’s matching procedure or asking SQL superheroes like us for help. Since we work exclusively with top customers, the decision was made quickly:

Afterthoughts

With WordPress, everything is actually a post, as it was originally intended as a blogging CMS. Pages are actually only posts without taxonomy, i.e. without categories and keywords. WooCommerce products, on the other hand, are special posts (custom post types) with fields for price and stock added. All Post Types, Native and Custom, are stored in the wp_post table in the database. Additional information about the posts, SKU and GTIN of the products in this case, are stored in the table wp_postmeta. So there we find both the GTIN of Germanized as _ts_gtin and the WooCommerce-native article number as _sku.

To write the value of _ts_gtin to _sku, we do a so-called Selfjoin based on the post ID (post_id), since both source and target are in the same table. We only want to perform this operation on products and therefore limit ourselves to entries that have a _sku at all.

With such delicate operations, which could destroy the entire database, it is extremely important to develop and test the process in a sandbox. And before the process is then applied in the productive system, it must be ensured that the state before the execution of the SQL statement can be restored. Under certain circumstances by

Such workflows are absolutely necessary for systems that are constantly changing. High-turnover online shops that continuously receive orders for example.

This works not only with WooCommerce, by the way. Many plugins, and even WordPress itself, often store post information in the metadata. And if you have a handle on manipulation at the database level, you can often complete tasks in minutes that would otherwise take days and thus cost a lot of money.

We hope we have been able to bring you a little closer to SQL in connection with WordPress metadata. Perhaps this article is the first step on your personal path to becoming a database wizard.

Share

Share on facebook
Share on twitter
Share on linkedin

Related Posts

eCommerce Sales

5 eCommerce Lockdown Tips

eCommerce is booming because since Corona everyone understands the importance of online stores and digitization. We have been supporting online stores since the beginning of the millennium, when you still had to program them yourself, and we would like to tell you here how your eCommerce tools can make good sales and keep you afloat in a crisis.

QR Code

Text to Table via Shortcode

Shortcodes still play a major role for developers in WordPress in 2020. In this article we learn how a shortcode can retrieve content from a custom field and output it formatted.

GET IN TOUCH

Contact.