Cross table delete with mysql

Question

When I execute the following query:

DELETE FROM wp_postmeta 
    WHERE post_id 
        NOT IN (
            SELECT post_id 
            FROM wp_postmeta a 
            INNER JOIN wp_sl_posts b 
            ON a.post_id = b.id
            )

I get an error message:

You can't specify target table 'wp_postmeta' for update in FROM clause

I sort of know why, but I really don't know the syntax required here to perform the operation. I've tried a few different alternatives based on answers here and elsewhere, but not luck.

http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause — ᴘᴀɴᴀʏɪᴏᴛɪs, Apr 28 '12 at 20:16

juergen d · Accepted Answer · 2012-04-28T20:32:04.313

2

try

DELETE wp_postmeta 
FROM wp_postmeta 
left outer JOIN wp_sl_posts b ON a.post_id = b.id
WHERE b.post_id is null

edited Apr 28 '12 at 20:32

answered Apr 28 '12 at 20:17

juergen d

195,137
36
275
343

score 1 · Answer 2 · answered Apr 28 '12 at 20:36

1

This is basically the same, and should work:

DELETE FROM wp_postmeta
WHERE post_id NOT IN (SELECT id FROM wp_sl_posts)

answered Apr 28 '12 at 20:36

bfavaretto

70,503
15
107
148

Cross table delete with mysql

2 Answers2