Hi Hank,
I think your problem is to find all the files under /uploads by a command like :
cd /uploads
ls -lR | grep ".doc" >files.txt
then load the files.txt into a temporary table TEMPtable that you create for
this issue (see http://dev.mysql.com/doc/mysql/en/load-data.html), after
truncating it.
When data is loaded, you can then delete by :
delete from Your_table where attachement not in (select attachement from
TEMPtable);
commit;
to rewrite a "not in", see
http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html
AND look at use of tempfalg at the bottom of the page.
Mathias
Selon Hank <heskin@stripped>:
> I have a table of school class assignments with the following fields -
> the first four fields are the primary key:
>
> Year (int)
> Term (enum, "Spring", "Fall","Winter")
> ClassID (int)
> SectionID (int)
> Attachement (varchar 225)
>
> The "attachment" field is a pointer to a file in the OS of an uploaded
> file, in the format like this:
> "/uploads/2003/Fall/330/1/conversions.doc"
>
> When an old class item is imported into a new class item for a new
> year/term/class/section (new record), the "attachment" field is copied
> over, and the OS file stays where it is.. the attachment field value
> is then a pointer the to the OS file for a previous year/term (i.e.
> the OS file is not copied to the new location filesystem structure).
>
> I've been trying to construct a self-joining query to list all the
> attachments which are NOT referenced by some future
> year/term/class/section. The desired query result is a list of files
> I can DELETE - i.e. files not imported or being pointed to by any
> other class_item record in a different year/term.
>
> Keep in mind that pointer to files in the same Year/Term (but
> different class/section) are NOT to be deleted.
>
> The system currently has MySQL version 4.0.1, so I can't use
> subqueries (i.e. NOT IN (...)).
>
> Any suggestions would be greatly appreciated. thanks.
> -Hank
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
