0

There are a couple of questions asked at SO similar like this but I have followed them but no luck.

I am trying to update my wordpress posts table column from postmeta value.

posts table

| ID | ... |     order_date     |
|----|-----| ------------------ |
| 1  | ... | 2019-07-01 08:10:00|

I need to update this new order_date column from old meta_value data.

I have followed these questions and answers.

mysql update column with value from another table

MySql Update one table from another fails

UPDATE posts 
SET posts.order_date= ( SELECT meta_value FROM postmeta WHERE post_id = posts.ID AND meta_key = 'order_date' ) 
WHERE post_type = 'orders' 
LIMIT 1000

AND

UPDATE posts  
SET posts.order_date = (
    SELECT meta_value 
    FROM postmeta
    WHERE post_id = posts.ID 
      AND meta_key = '_order_date'
)
WHERE post_type = 'orders' AND order_date IS NULL

But no luck '0 Rows affected'.

Hope someone can help me.

Thank you

Fortunaglobal Development
  • 103
  • 1
  • 2
  • 8
  • First of all, run your subquery separately and check whether it's returning some record or not. if it will return more than 1 record it won't update your data. possible error "error code 1242 subquery returns more than 1 row". It may help you. https://stackoverflow.com/questions/28171474/solution-to-subquery-returns-more-than-1-row-error – Niklesh_Chauhan Jul 22 '19 at 13:48
  • @Niklesh_Chauhan I have checked and it returns the correct result. – Fortunaglobal Development Jul 22 '19 at 14:34
  • is `WHERE post_type = 'orders' AND order_date IS NULL` probably ambiguous? do you need to use `posts.post_type` instead? – Cyber Jul 22 '19 at 14:42

0 Answers0