Interesting Database Query Question!!

[Jay]

Hello,

I have an interesting design problem, I would like to have your opinion on.

I have table DRAWINGS, which has a field DrawingName which contains a list
of drawings with thier full path.
Eg- c:\winnt\mydrawing\room.dwg

I have a table ATTRIBUTES which has a field DrawingName which contains a
list of drawings without thier full path.
Eg- room.dwg

Now my objective is to delete all records in ATTRIBUTES table whose
ATTRIBUTES.DrawingName is not present in DRAWINGS.DrawingName.


When I initially implemented it I manually iterated the records to
accomplish it, Iam trying to find a better way to do it.

Thanks.
jay

 

[Chris Taylor]

Hi,

The following should do what I understand you require

delete from attributes
where not exists ( select 1
from Drawings
where right(DrawingName,len(attributes.DrawingName)+1 )
= '\'+attributes.DrawingName )

I included the '\' in the test to ensure that there are not false positives
where the end of one filename matches another filename.

Hope this helps

Chris Taylor