List:General Discussion« Previous MessageNext Message »
From:mfatene Date:May 24 2005 10:15pm
Subject:Re: classic outer join problem
View as plain text  
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
>
>


Thread
data length vs index length ??Michael Gale24 May
  • Re: data length vs index length ??mfatene24 May
  • Re: data length vs index length ??Dan Nelson24 May
  • Re: data length vs index length ??SGreen24 May
  • classic outer join problemHank24 May
    • Re: classic outer join problemSGreen24 May
    • Re: classic outer join problemmfatene25 May