I just upgraded to 2.1.2, which uses CatLinks as the basis of the total link count. This has revealed that I have a bunch of links (187) in the CatLinks table that are no longer in the Links table (I don't know how or why... maybe I once deleted a bunch using phpMyAdmin or something...). So, my total count is off (and it's a bit of wasted space in the db).

So, can anyone provide a MySQL statement that would check each LinkID in the CatLinks table for a matching ID in the Links table, and delete any that don't exist in the Links table?

(Suggestion to GT: It would be handy if the "Repair Tables" function did this automatically. I can't be the first person to have gotten the tables out of sync.)

Thanks,

~~~~~~~~~~~~~~~~~~~~~~
Jamie Marie
BuffyGuide.com

Hi,

Hmm, I think repair tables only catches the other situation (Links not in any categories).

You could run:

SELECT LinkID
FROM lsql_CatLinks LEFT OUTER JOIN lsql_Links ON LinkID = ID
WHERE ID IS NULL

that should give you a list of ID's to delete. You can't delete it all in one query unfortunately.

Cheers,

Alex
--
Gossamer Threads Inc.

Maybe a little bit of code using GT::SQL would be a nice little snippet to add to the resources section :)

Something like:


might work. This is untested, so don't uncomment the delete unless you are happy it works. =) (Edit: improved the code a bit).

Cheers,

Alex
--
Gossamer Threads Inc.

Worked perfectly. Thank you!

~~~~~~~~~~~~~~~~~~~~~~
Jamie Marie
BuffyGuide.com