Could not delete

[Crystal via AccessMonster.com]

Pelase help me with the delete query.

I have tbl plans and tbl category and they join by 3 fields (last name, fst3
and dob). Now I need to delete from tbl plans all the matching records found
by the join query. When I run delete query, I got error msg: could not delete
from specified tables. I changed the property to distinctrow but it did not
work.The following SQL gives error msg:

DELETE DISTINCTROW a.*
FROM [plans] AS a INNER JOIN [category] AS b ON (a.[Last Name] = b.[Last Name]
) AND (a.fst3 = b.fst3) AND (a.DOB = b.DOB);

I cannot use IN because I need all three fields from the two tables match. So
I changed my query to use EXISTS, but it deletes all the records from the
table instead of just the ones matching between the two tables). When I run
the SQL after EXISTS, it gives me just the right amount of records. This SQL
delete everything:

delete *
FROM [04b plans]
where exists
(select * from [04b plans] c
INNER JOIN [05c blue covg] d ON c.DOB =d.DOB AND c.fst3 =d.fst3 AND c.[Last
Name] =d.[Last Name])

I have searched this wonderful site and could not find a solution. Thank you
very much for your help.

 

[Crystal via AccessMonster.com]

I have solved the delete problem. I was trying to delete from a table by
joining the table with a query so I got that "could not delete" error msg.
Once I change that qry to a tbl, I could delete just fine.

But please help me with the Exist problem mentioned in the last posting. Why
would it delete everything?

Thanks!

Pelase help me with the delete query.

I have tbl plans and tbl category and they join by 3 fields (last name, fst3
and dob). Now I need to delete from tbl plans all the matching records found
by the join query. When I run delete query, I got error msg: could not delete
from specified tables. I changed the property to distinctrow but it did not
work.The following SQL gives error msg:

DELETE DISTINCTROW a.*
FROM [plans] AS a INNER JOIN [category] AS b ON (a.[Last Name] = b.[Last Name]
) AND (a.fst3 = b.fst3) AND (a.DOB = b.DOB);

I cannot use IN because I need all three fields from the two tables match. So
I changed my query to use EXISTS, but it deletes all the records from the
table instead of just the ones matching between the two tables). When I run
the SQL after EXISTS, it gives me just the right amount of records. This SQL
delete everything:

delete *
FROM [04b plans]
where exists
(select * from [04b plans] c
INNER JOIN [05c blue covg] d ON c.DOB =d.DOB AND c.fst3 =d.fst3 AND c.[Last
Name] =d.[Last Name])

I have searched this wonderful site and could not find a solution. Thank you
very much for your help.

 

[giorgio rancati]

I have solved the delete problem. I was trying to delete from a table by
joining the table with a query so I got that "could not delete" error msg.
Once I change that qry to a tbl, I could delete just fine.

But please help me with the Exist problem mentioned in the last posting. Why
would it delete everything?

[CUT]

Hi Crystal ,

try this
----
delete *
FROM [04b plans] c
where exists
(select * from [05c blue covg] d
where c.DOB =d.DOB
AND c.fst3 =d.fst3
AND c.[Last Name] =d.[Last Name])

 

[John Spencer]

Crystal,
EXISTS checks to see if any record is being returned by the subquery.

Your subquery

(select * from [04b plans] c
INNER JOIN [05c blue covg] d ON c.DOB =d.DOB AND c.fst3 =d.fst3 AND c.[Last
Name] =d.[Last Name])

would always return rows as long as there were matches between the two
tables [04b plans] and [05c blue covg]

You needed to include a where clause in the subquery that referenced the
table and fields in your main query. Probably something like the following
UNTESTED SQL

DELETE *
FROM [04b plans] as A
WHERE exists
(SELECT *
FROM [05c blue covg] As D
WHERE ON A.DOB =d.DOB
AND A.fst3 =d.fst3
AND A.[Last Name] =d.[Last Name])

I have solved the delete problem. I was trying to delete from a table by
joining the table with a query so I got that "could not delete" error msg.
Once I change that qry to a tbl, I could delete just fine.

But please help me with the Exist problem mentioned in the last posting.
Why
would it delete everything?

Thanks!

Pelase help me with the delete query.

I have tbl plans and tbl category and they join by 3 fields (last name,
fst3
and dob). Now I need to delete from tbl plans all the matching records
found
by the join query. When I run delete query, I got error msg: could not
delete
from specified tables. I changed the property to distinctrow but it did
not
work.The following SQL gives error msg:

DELETE DISTINCTROW a.*
FROM [plans] AS a INNER JOIN [category] AS b ON (a.[Last Name] = b.[Last
Name]
) AND (a.fst3 = b.fst3) AND (a.DOB = b.DOB);

I cannot use IN because I need all three fields from the two tables match.
So
I changed my query to use EXISTS, but it deletes all the records from the
table instead of just the ones matching between the two tables). When I
run
the SQL after EXISTS, it gives me just the right amount of records. This
SQL
delete everything:

delete *
FROM [04b plans]
where exists
(select * from [04b plans] c
INNER JOIN [05c blue covg] d ON c.DOB =d.DOB AND c.fst3 =d.fst3 AND
c.[Last
Name] =d.[Last Name])

I have searched this wonderful site and could not find a solution. Thank
you
very much for your help.

 

[Crystal via AccessMonster.com]

Thank you both, Giorgio and John for the code and explanation. The code works
great and I understand Exist now. I am always grateful for this community and
helpful experts like you guys.

Crystal,
EXISTS checks to see if any record is being returned by the subquery.

Your subquery

(select * from [04b plans] c
INNER JOIN [05c blue covg] d ON c.DOB =d.DOB AND c.fst3 =d.fst3 AND c.[Last
Name] =d.[Last Name])

would always return rows as long as there were matches between the two
tables [04b plans] and [05c blue covg]

You needed to include a where clause in the subquery that referenced the
table and fields in your main query. Probably something like the following
UNTESTED SQL

DELETE *
FROM [04b plans] as A
WHERE exists
(SELECT *
FROM [05c blue covg] As D
WHERE ON A.DOB =d.DOB
AND A.fst3 =d.fst3
AND A.[Last Name] =d.[Last Name])

I have solved the delete problem. I was trying to delete from a table by
joining the table with a query so I got that "could not delete" error msg.

[quoted text clipped - 43 lines]