delete statement for unmatched records in 2 tables. 1

[kavya]

Hi,

I have two tables tblsub and tblun they have a common field name 'filename'. I am trying to delte records in tblsub, if a record with filename is not found in tblun.
Tried different ways but still getting error.

Thanks in advance

 

[mwolf00]

DELETE FROM tblSub WHERE fileName NOT IN (SELECT fileName FROM tblUn) -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[kavya]

Thanks for your reply it worked.

 

[tlbroadbent]

Be careful when using &quot;Not In (Select col from table)&quot;. This can create erroneous results if null values exist in the table of the subquery. See the article at

http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/CEEA6CCC09585570852568E20077EC44

It is safer to use Not Exists.

DELETE FROM tblSub
WHERE Not Exists
(SELECT * FROM tblUn Where fileName=tblSub.fileName) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.