Delete query on derived left join

Databases
1

Select returns the right results but I am unable to delete records by below query. Any help much appreciated:

delete wp_posts_cpy,wp_postmeta_cpy from (select * from wp_postmeta_cpy meta inner join wp_posts_cpy p ON(meta.post_id=p.id) where p.post_type='co-listing' and meta.meta_key='agent_id') p left join wp_posts_cpy agent ON(p.meta_value=agent.id) where
agent.post_title IS NULL
2

try like this –

DELETE wp_posts_cpy
     , wp_postmeta_cpy 
  FROM wp_postmeta_cpy meta 
INNER 
  JOIN wp_posts_cpy p 
    ON p.id = meta.post_id 
   AND p.post_type = 'co-listing' 
LEFT 
  JOIN wp_posts_cpy agent 
    ON agent.id = p.meta_value
 WHERE meta.meta_key = 'agent_id'  
   AND agent.post_title IS NULL

if further issues, try removing all the table aliases

3

Error is still same:

Unknown table ‘wp_posts_cpy’ in MULTI DELETE

4

https://dev.mysql.com/doc/refman/5.7/en/delete.html

From example below the site I anderstand that correct is:

DELETE p, meta FROM...
5

Perfect many thanks to all for great help.
Yes it worked this way DELETE p, meta FROM…

6

it would have worked if you had tried this, too –

7

AFAIK You can only delete one ID at a time: delete wp_posts_id from…

In this case I use CTE instead. Make a list and delete referring to this list:

WITH list AS (Select id from conditions)
delete from table where id in (select * from list)
8

i gotta be honest, i haven’t tested a multi-table delete myself, but da manual says you can do it

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...       /* NOTE MULTIPLE TABLES */
    FROM table_references
    [WHERE where_condition]

also, please note, you do not delete a column (wp_posts_id) but rather a table, i.e. rows from a table

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.