Interesting Database Query Question!!

  • Thread starter Thread starter Jay
  • Start date Start date
J

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
 
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
 
hi jay!
i tried the query by "Chris Taylor", i guess it dose too
much deleteing work removing every row in ATTRIBUTES table
jay, i don't know which database ur using
but following query will work sql server and will do what
u want.
DELETE FROM ATTRIBUTEs
WHERE (DrawingName NOT IN
(SELECT RIGHT(DrawingName,
CHARINDEX('\', REVERSE(DrawingName)) - 1) AS Expr1
FROM DRAWINGS))
In case u r using any other database u will have to
replace query with appropriate string functions, but rest
of the sql construct will remain same, IN clause although
expensive(performance wise) will have to be used, i can't
think of any other options here.
 
Hi,

Interesting, just tested the query again and it only deletes the attributes
which are not referenced in the Drawing table.
I believe EXISTS would yield better performance than using an IN statement.
Maybe I am missing something.

Regards

Chris Taylor
 
Back
Top