Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Bulk delete

Quote Reply
Bulk delete
I need to delete old records, for example records which are older than 6 months. With Links2.0 (sometimes I miss it so much) I just copied the links.db, opened the file with my wordpad and put away the old records.

Is there any way to define a date, and delete all records before that date, without marking each record manual?

Michael

Quote Reply
Re: Bulk delete In reply to
You can do that in SQL very easily, but you need to also remove the CatLink record. Alex has been improving the repair functions, and it's possible that bulk-delete based on some condition will be added in as an admin option (if you do it that way, it's "almost" trivial, just a matter of interface), but trying to manually duplicate it is rough.

Btw... you can bulk delete the records by issuing a command:

DELETE from Links Where Add_Date <= '2000-08-25'

The problem is it won't do a delete from the CatLinks, which means there will be orphan records, until "cleaned" up.

PUGDOG® Enterprises, Inc.
FAQ:http://LinkSQL.com/FAQ
Forum:http://LinkSQL.com/forum
Quote Reply
Re: Bulk delete In reply to
With other words, it could be a part of next version, isn´t it?

I don´t want to have trouble ....

Michael

Quote Reply
Re: Bulk delete In reply to
Yes and no. It's not something alex is specifically doing, but he's been adding more features to the table repair -- ie: orphan and widow catching, so if you did a bulk SQL delete as I indicated, then did a "repair" the program would remove the unused CatLink records since they are missing the "link" they are pointing to.

A CatLink record holds two integers:

CategoryID:LinkID

If you remove the Link record that has the LinkID as the ID number, then the record in the CatLink table will not point to anything.

On small to medium sized databases you could probably do something like:

DELETE FROM CatLinks
WHERE LinkID NOT IN (SELECT ID FROM Links)

Without too much of a performance hit. "IN" is available in Mysql 3.21+

_NEVER_ try DELETE statements out!! Always change the DELETE to a SELECT * until the Query returns the values you want, _THEN_ and _ONLY_THEN_ try the DELETE !!! You can't recover from a DELETE... so use it only when you know the query works the way you want!



PUGDOG® Enterprises, Inc.
FAQ:http://LinkSQL.com/FAQ
Forum:http://LinkSQL.com/forum
Quote Reply
Re: Bulk delete In reply to
In Reply To:
With Links2.0 (sometimes I miss it so much) I just copied the links.db, opened the file with my wordpad and put away the old records.
MySQL...and LINKS SQL are so much easier after you get the hang of SQL....

I would recommend doing some reading on SQL commands that you can use in the MySQLMan (or table maintenance) script...SQL for Dummies is a really good starting point, IMHO....

I used Links 2.0 and other flat file systems for three years...won't go back after using MySQL and LINKS SQL.

Wink

Regards,

Eliot Lee