3

I'm using this query to select certain row from the wp_postmeta table

SELECT *
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.meta_value
WHERE (wp.ID IS NULL) AND (pm.meta_key = '_wpcf_belongs_books_id')

and it's working fine but when I try to use the DELETE

DELETE
FROM wp_postmeta pm
LEFT JOIN fddb_wp_posts wp ON wp.ID = pm.meta_value
WHERE (wp.ID IS NULL) AND (pm.meta_key = '_wpcf_belongs_books_id')

it returns an error saying

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pm LEFT JOIN fddb_wp_posts wp ON wp.ID = pm.meta_value WHERE (wp.ID IS NULL) AND' at line 2

Any suggestion on how to make it work?

carletto0282
  • 33
  • 5
  • @Strawberry: Deleting records having no relation to another table - seems ok to me. Note the `WHERE wp.ID IS NULL` – juergen d Feb 27 '17 at 11:44
  • @juergend I guess I'm just used to CASCADE handling that sort of thing. – Strawberry Feb 27 '17 at 11:47
  • Hi @Cheslab I've search before posting but I didn't find anything mentioning the error #1064 ... and since I'm not good at all in mysql it looks like that question was different from mine. – carletto0282 Feb 28 '17 at 10:48
  • @carletto0282 Error #1064 just means that MySQL can't understand your command. It's a quite common error. I understand that you may see these questions are different, but for me they are pretty much the same. Eventually you've got the solution for your problem. – Cheslab Feb 28 '17 at 18:36
  • @Cheslab yes I've got the solution and as you can see I confirmed this question is a duplicate. I was just trying to explain why I've created a new question despite the other one. Of course this is a proof I'm a real newbie in mysql... thank you anyway. – carletto0282 Mar 01 '17 at 01:53

2 Answers2

1

If you have a delete statement with more than one table you need to name the tables you want to delete from. Do that right after the delete keyword

DELETE pm
FROM fddb_wp_postmeta pm ...
juergen d
  • 201,996
  • 37
  • 293
  • 362
0
DELETE
FROM fddb_wp_postmeta  where 
meta_value not in  (select wp.ID from fddb_wp_posts wp)
  and meta_key = '_wpcf_belongs_books_id'
VIPAN SABHERWAL
  • 170
  • 10
  • 1
    Hi @vipan-sabherwal thanks... I like very much your suggestion since it let me select rows manually for deleting! – carletto0282 Feb 28 '17 at 10:50