CODEWITHSUNDEEP
mysqldrupalsql-delete
Sammy
Sammy

Reputation: 967

Need to delete unreferenced rows from a Drupal MySQL Database

I am running a PHP MySQL command script (cleanup.php) via cron that deletes the duplicate and BS content from the aggregator_item table (Table 2 below). The problem is that over time the aggregator_category_item table (Table 1 below) contains too many unreferenced rows.

Ideally, Table 1 and Table 2 should have the same number of rows.

I need a MySQL command, mysql_query(), that would delete all the rows in the aggregator_category_item table (Table 1) for the following condition:

If the idd number in aggregator_category_item table (Table 1) is NOT found within the aggregator_item table (Table 2), then delete these unreferenced rows from the aggregator_category_item table (Table 1). I would like to add this MySQL delete command to my current MySQL command cron script.

Below are the following two tables in the Drupal Database:

Table 1: aggregator_category_item (145,000 rows)
-----------------------------------------------
iid        cid
6644403    2


Table 2: aggregator_item (8700 rows)
------------------------------------
iid      fid  title  link       author  description  timestamp
6644403  25   hello  http://...         hello there  1348243145

Upvotes: 0

Views: 566

Answers (1)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171411

delete from aggregator_category_item
where iid not in (select iid from aggregator_item)

Upvotes: 2

Related Questions