SQL – how to remove lots of data from one table using a reference for another.

  • Resolved Zaphod

    (@zaphod)


    14 years, 1 month ago

    I’m using WP for a car site.

    I have the cc_posts table and another table cc_posts_detail that I use for, well, car details.

    Currently there are over 45k cars on the system which come in from three sources. I’m trying to write a query that will delete all post entries and post_detail entries as follows but I think there’s something wrong with it as it always seems to lock up the db.

    delete from cc_posts where id in (SELECT post_id
FROM <code>cc_posts_detail</code>
WHERE referrer_site = "vendor1")

    Is there anything wrong with the query?

Viewing 2 replies - 1 through 2 (of 2 total)
  • Thread Starter Zaphod

    (@zaphod)

    14 years, 1 month ago

    Sorry that query should obviously be:

    delete from cc_posts where id in (SELECT post_id
FROM cc_posts_detail
WHERE referrer_site = "vendor1")
    Thread Starter Zaphod

    (@zaphod)

    14 years, 1 month ago

    Ok, solved this myself. While I think the SQL is actually fine, the problem is that there were thousands of matches for the inner select, and the servers seemed unable to deal with deleting 10k records with such a query – I think due to memory restrictions. Fair enough.

    So I basically rewrote the thing to get all the ids and then output one sql query at a time with each id. Works a treat.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘SQL – how to remove lots of data from one table using a reference for another.’ is closed to new replies.

Tags