Question 336974 in Wordpress Question posted in
The official Wordpress documentation can be found here.

WordPress – How to update WP post_meta products sale price with one query, if _sale_price exists update it, if not insert new row

In WordPress table wp_postmeta products data is stored like this:

meta_id post_id meta_key meta_value
6677055 36828 _product_version 4.7.0
6677056 36828 _price 439.2
6677057 36828 _wp_trash_meta_comments_status a:0:{}
6677058 36828 _wp_desired_post_slug notebook-black-spire-v-nitro-vn7-591g
6677059 36828 _wxr_import_has_attachment_refs 1
6677060 36828 _wp_page_template default
6677049 36828 _virtual no
6677047 36828 _backorders no
6677048 36828 _sold_individually yes
6677053 36828 _stock NULL
6677052 36828 _download_expiry -1
6677050 36828 _downloadable no
6677043 36828 _sale_price 439.2
6677042 36828 _regular_price 1596

Sometimes products have a row _sale_price with meta_value=SOME_NUMBER

Values for sale price are changing daily and I want to make a MySQL query that will behave like this:

For post_id=36828 update meta_value WHERE meta_key="_sale_price"
IF meta_key="_sale_price" doesn't exist insert it.

I have tried doing it like this – updatin old value 439.2 -> 100:

SELECT meta_value, REPLACE (meta_value,439.2,100) FROM `wp_postmeta` WHERE post_id=36828 and meta_key="_sale_price";

but that didn’t do anything, meaning that after I run this query I get as if it is successful and see this: https://dbfiddle.uk/t7W5AB9V (thank you @nbk)

but when I refresh my table I don’t see a change in the row meta_id=6677043.

Do you have any suggestions or is the only proper way to search for existing entry and then update if it exists or insert if it doesn’t?

Tags:

2

Answers


  1. 0

    You could use a dynamic approach

    sample First for update (with row)

    CREATE TABLE wp_postmeta (
  `meta_id` INTEGER primary key auto_increment,
    `post_id` INTEGER,
  `meta_key` VARCHAR(31),
  `meta_value` VARCHAR(37)
);

INSERT INTO wp_postmeta
  (`meta_id`, `post_id`, `meta_key`, `meta_value`)
VALUES
  ('6677055', '36828', '_product_version', '4.7.0'),
  ('6677056', '36828', '_price', '439.2'),
  ('6677057', '36828', '_wp_trash_meta_comments_status', 'a:0:{}'),
  ('6677058', '36828', '_wp_desired_post_slug', 'notebook-black-spire-v-nitro-vn7-591g'),
  ('6677059', '36828', '_wxr_import_has_attachment_refs', '1'),
  ('6677060', '36828', '_wp_page_template', 'default'),
  ('6677049', '36828', '_virtual', 'no'),
  ('6677047', '36828', '_backorders', 'no'),
  ('6677048', '36828', '_sold_individually', 'yes'),
  ('6677053', '36828', '_stock', NULL),
  ('6677052', '36828', '_download_expiry', '-1'),
  ('6677050', '36828', '_downloadable', 'no'),
  ('6677043', '36828', '_sale_price', '439.2'),
  ('6677042', '36828', '_regular_price', '1596');

    Records: 14  Duplicates: 0  Warnings: 0

    SELECT `meta_id`, `post_id`, `meta_key`, `meta_value`
  FROM `wp_postmeta` 
  WHERE post_id=36828 and meta_key="_sale_price";
    meta_id post_id meta_key meta_value
    6677043 36828 _sale_price 439.2 
    SELECT
if (exists(SELECT 1 from wp_postmeta WHERE post_id=36828 and meta_key="_sale_price" ) 
, @sql := 'UPDATE `wp_postmeta` 
  SET meta_value = 100
WHERE post_id=36828 and meta_key="_sale_price" ',
@sql := 'INSERT INTO wp_postmeta ( `post_id`, `meta_key`, `meta_value`) VALUES (36828,"_sale_price",100) ');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    if (exists(SELECT 1 from wp_postmeta WHERE post_id=36828 and meta_key="_sale_price" )
    , @sql := ‘UPDATE `wp_postmeta`
      SET meta_value = 100
    WHERE post_id=36828 and meta_key="_sale_price" ‘,
    @sql := ‘INSERT INTO wp_postmeta ( `post_id`, `meta_key`, `met
    UPDATE `wp_postmeta`
      SET meta_value = 100
    WHERE post_id=36828 and meta_key="_sale_price"    		 
    Statement prepared

    Rows matched: 1  Changed: 1  Warnings: 0

    SELECT `meta_id`, `post_id`, `meta_key`, `meta_value`
  FROM `wp_postmeta` 
  WHERE post_id=36828 and meta_key="_sale_price";
    meta_id post_id meta_key meta_value
    6677043 36828 _sale_price 100

    fiddle

    Sample for insert (where the row is missing

    CREATE TABLE wp_postmeta (
  `meta_id` INTEGER primary key auto_increment,
    `post_id` INTEGER,
  `meta_key` VARCHAR(31),
  `meta_value` VARCHAR(37)
);

INSERT INTO wp_postmeta
  (`meta_id`, `post_id`, `meta_key`, `meta_value`)
VALUES
  ('6677055', '36828', '_product_version', '4.7.0'),
  ('6677056', '36828', '_price', '439.2'),
  ('6677057', '36828', '_wp_trash_meta_comments_status', 'a:0:{}'),
  ('6677058', '36828', '_wp_desired_post_slug', 'notebook-black-spire-v-nitro-vn7-591g'),
  ('6677059', '36828', '_wxr_import_has_attachment_refs', '1'),
  ('6677060', '36828', '_wp_page_template', 'default'),
  ('6677049', '36828', '_virtual', 'no'),
  ('6677047', '36828', '_backorders', 'no'),
  ('6677048', '36828', '_sold_individually', 'yes'),
  ('6677053', '36828', '_stock', NULL),
  ('6677052', '36828', '_download_expiry', '-1'),
  ('6677050', '36828', '_downloadable', 'no'),
  ('6677042', '36828', '_regular_price', '1596');

    Records: 13  Duplicates: 0  Warnings: 0

    SELECT `meta_id`, `post_id`, `meta_key`, `meta_value`
  FROM `wp_postmeta` 
  WHERE post_id=36828 and meta_key="_sale_price";
    meta_id post_id meta_key meta_value 
    SELECT
if (exists(SELECT 1 from wp_postmeta WHERE post_id=36828 and meta_key="_sale_price" ) 
, @sql := 'UPDATE `wp_postmeta` 
  SET meta_value = 100
WHERE post_id=36828 and meta_key="_sale_price" ',
@sql := 'INSERT INTO wp_postmeta ( `post_id`, `meta_key`, `meta_value`) VALUES (36828,"_sale_price",100) ');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    if (exists(SELECT 1 from wp_postmeta WHERE post_id=36828 and meta_key="_sale_price" )
    , @sql := ‘UPDATE `wp_postmeta`
      SET meta_value = 100
    WHERE post_id=36828 and meta_key="_sale_price" ‘,
    @sql := ‘INSERT INTO wp_postmeta ( `post_id`, `meta_key`, `met
    INSERT INTO wp_postmeta ( `post_id`, `meta_key`, `meta_value`) VALUES (36828,"_sale_price",100) 
    Statement prepared

    SELECT `meta_id`, `post_id`, `meta_key`, `meta_value`
  FROM `wp_postmeta` 
  WHERE post_id=36828 and meta_key="_sale_price";
    meta_id post_id meta_key meta_value
    6677061 36828 _sale_price 100

    fiddle

    more dynamic is when you make id post_id and the price variable

    Login or Signup to reply.
  2. 0

    MySQL supports two forms of insert-or-replace: REPLACE and INSERT…ON DUPLICATE KEY UPDATE.

    But both of these statements don’t know what constitutes a duplicate unless a primary key or unique key is defined in the table, such that when you insert a row, the value in the primary/unique key conflicts with another row.

    In the case of the standard wp_postmeta table, there’s a primary key on meta_id, but no other unique key. So if you insert a new row with the same meta_key of ‘_sale_price’, it won’t count as a duplicate. If the table had a unique key over (post_id, meta_key), then it would only allow one row per post with a given value for meta_key, and inserting a second time with the same value would trigger the behavior for duplicates.

    But if you define such a unique key, it would apply to all meta_key values, not just ‘_sale_price’. We don’t know that there aren’t other meta_keys for which it is normal to allow multiple rows.

    So the only solution I can recommend is to do your update in two queries.

    1. SELECT for the row with the ‘_sale_price’ key for the respective post.
    2. If no row is found (i.e. the result of the SELECT is empty), then INSERT a row.
    3. Otherwise UPDATE the existing row.

    This method has an unavoidable race condition. If two sessions are doing the same operation concurrently, they could both do their SELECT and think there is no row. Then both INSERT a row. The chances of this race condition may be low enough that you are willing to accept the risk.

    Or you could add table-locking logic to prevent multiple sessions from doing the action at the same time.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search