A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Delete records from second table



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2010, 08:02 PM posted to microsoft.public.access.queries
sebastico
external usenet poster
 
Posts: 74
Default Delete records from second table

Hello
I have two Tables.
tblMain(MainID)
tblSec(FamID, Group, MainID)
MainID has same attribute in both tables
tblMain has the correct records.

How can I delete all records from tblSec not present in tblMain? Could you
tell me the kind of query. I'm studying Relational Algebra by myself and this
example would help me to learn
Thanks in advance
  #2  
Old May 28th, 2010, 08:33 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Delete records from second table

Do you want to delete the records from the table or just SHOW the records?

SELECT tblSec.*
FROM tblSec INNER JOIN tblMain
ON tblSec.MainID = tblMain.MainID


DELETE
FROM TblSec
WHERE MainID NOT IN (SELECT MainID FROM tblMain)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

sebastico wrote:
Hello
I have two Tables.
tblMain(MainID)
tblSec(FamID, Group, MainID)
MainID has same attribute in both tables
tblMain has the correct records.

How can I delete all records from tblSec not present in tblMain? Could you
tell me the kind of query. I'm studying Relational Algebra by myself and this
example would help me to learn
Thanks in advance

  #3  
Old May 28th, 2010, 10:36 PM posted to microsoft.public.access.queries
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Delete records from second table

Just to add one thing to John's reply; a NOT IN operation will not work if
the subquery returns at least one NULL. This is because:

x NOT IN(a,b,c)

is the equivalent of:

xa AND x b AND x c

so all three non-equality operations have to evaluate to TRUE. If we
substitute real vales:

42 NOT IN (50, 100, 150)

which is the equivalent of:

42 40 AND 42 100 AND 42 150

then this is fine as it evaluates as:

TRUE AND TRUE AND TRUE

so the whole Boolean expression evaluates to TRUE. But if we substitute:

42 NOT IN (50, NULL, 150)

which is the equivalent of:

42 40 AND 42 NULL AND 42 150

then this evaluates as:

TRUE AND NULL AND TRUE

so the expression as a whole evaluates to NULL, neither TRUE nor FALSE.

The way to avoid this is to use the NOT EXISTS predicate:

DELETE *
FROM tblSec
WHERE NOT EXISTS
(SELECT * FROM tblMain
WHERE tblMain.MainID = tblSec.MainID);

The problem with Nulls does not arise with an IN operation of course as
that's the equivalent of:

x = a OR x = b OR x = c

so the expression evaluates to TRUE if any one of the equality operations is
TRUE. Nevertheless the EXISTS predicate will normally perform faster than an
IN operation.

Ken Sheridan
Stafford, England

sebastico wrote:
Hello
I have two Tables.
tblMain(MainID)
tblSec(FamID, Group, MainID)
MainID has same attribute in both tables
tblMain has the correct records.

How can I delete all records from tblSec not present in tblMain? Could you
tell me the kind of query. I'm studying Relational Algebra by myself and this
example would help me to learn
Thanks in advance


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201005/1

  #4  
Old June 2nd, 2010, 04:06 PM posted to microsoft.public.access.queries
sebastico
external usenet poster
 
Posts: 74
Default Delete records from second table


John And Ken

Many thanks for you help.
John your sql code works and the information by Ken is very useful as well
"John Spencer" wrote:

Do you want to delete the records from the table or just SHOW the records?

SELECT tblSec.*
FROM tblSec INNER JOIN tblMain
ON tblSec.MainID = tblMain.MainID


DELETE
FROM TblSec
WHERE MainID NOT IN (SELECT MainID FROM tblMain)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

sebastico wrote:
Hello
I have two Tables.
tblMain(MainID)
tblSec(FamID, Group, MainID)
MainID has same attribute in both tables
tblMain has the correct records.

How can I delete all records from tblSec not present in tblMain? Could you
tell me the kind of query. I'm studying Relational Algebra by myself and this
example would help me to learn
Thanks in advance

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.