-2

I have this table:

IMG1

I want to copy the value from key "_regular_price" to the value of key "_price". The end result wanted is the "_price" value to be "100" in this example.

I have tried with:

UPDATE 
  `wp_postmeta` 
SET 
  `_price`= `_regular_price` 
WHERE 
  `meta_key`="_price" AND `meta_value`=""

But this inserts "_regular_price" and not the value of "_regular_price".

How to fix this query?

Help me SQL ninjas :)

JohnyL
  • 6,894
  • 3
  • 22
  • 41
prokops
  • 45
  • 6
  • Are you sure you really have there backticks (as in the code above) and not quotes? It should work, it's syntactically ok. – pavel Jun 12 '19 at 11:49
  • Include table description as text, not as a link to an image – Joakim Danielson Jun 12 '19 at 11:52
  • I just had the same problem a few hours ago, if you want a table column as a value, delete the backticks like panther said, but don't add any quotes. At least MySQL thinks anything in any quotes at place of values as a string, not as a column name. – kry Jun 12 '19 at 11:59
  • 1
    Which dbms are you using? ANSI SQL doesn't like backticks... – jarlh Jun 12 '19 at 12:05
  • MySQL/MariaDB most likely @jarlh that looks like a screenshot of PHPmyAdmin.. – Raymond Nijland Jun 12 '19 at 12:11
  • Possible duplicate of [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) – Raymond Nijland Jun 12 '19 at 12:13
  • I ended up fixing this via MySQL export/import and Excel operations to fix the values. As a sidenote, this question gets 6 upvotes: https://wordpress.stackexchange.com/questions/226436/sql-query-to-copy-value-of-a-meta-key-into-another-meta-key. My question gets 1 downvote. My conclusion is I apparently don't understand what stackoverflow is for or how to ask questions :O. – prokops Jun 12 '19 at 12:54

2 Answers2

0

In MySQL, SET works like

SET column = value

and if you put value inside quotes (or even backticks) it will think it's a string. MySQL has some kind "error fixing", where it automatically adds or removes quotes as needed or not, but at value it's not unequivocal, so assumes the user is correct in this case.

kry
  • 362
  • 3
  • 13
0

You need a self join in the UPDATE statement:

UPDATE 
  wp_postmeta t inner join wp_postmeta tt
  on t.`_sku` = '_price' AND tt.`_sku` = '_regular_price' 
WHERE coalesce(t.`63171718`, '') = '' 
SET 
  t.`63171718` = tt.`63171718`
forpas
  • 160,666
  • 10
  • 38
  • 76