Delete query on derived left join

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( where p.post_type='co-listing' and meta.meta_key='agent_id') p left join wp_posts_cpy agent ON( where
agent.post_title IS NULL

try like this –

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

if further issues, try removing all the table aliases

Error is still same:

Unknown table ‘wp_posts_cpy’ in MULTI DELETE

From example below the site I anderstand that correct is:

DELETE p, meta FROM...

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

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

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)

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

    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

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