delete query - could not delete from specified table

  • Thread starter Thread starter יריב החביב
  • Start date Start date
×

יריב החביב

Hello,

i am trying to delete (in delete query) records in a table.

where in the delete query the table is join (3 inner) to a query (select).

i get the message . . . "could not delete from specified table"

is it posibble ? where i am wrong ?

Thank's

תודה רבה
 
יריב החביב said:
Hello,

i am trying to delete (in delete query) records in a table.

where in the delete query the table is join (3 inner) to a query (select).

i get the message . . . "could not delete from specified table"

is it posibble ? where i am wrong ?

Thank's

תודה רבה
Click to expand...
 
The error means that ACCESS cannot identify the unique records that you want
to delete.

Post the SQL statement of the delete query that you're trying to run, and we
can show you how to rewrite it so that you can delete the desired records.
 
Thank You !

Here is the code,

DELETE BasicSalleryAllValues.*
FROM BasicSalleryAllValues INNER JOIN QMonth_Key_choice ON
(BasicSalleryAllValues.Sub_Timing = QMonth_Key_choice.Timing_calc) AND
(BasicSalleryAllValues.Timing = QMonth_Key_choice.CalcPeriod) AND
(BasicSalleryAllValues.FixedDate = QMonth_Key_choice.FixedDate);
 
Thank You Very Very Much ! ! !
--
תודה רבה


KenSheridan via AccessMonster.com said:
This should do it:

DELETE *
FROM BasicSalleryAllValues
WHERE EXISTS
(SELECT *
FROM QMonth_Key_choice
WHERE QMonth_Key_choice.Timing_calc
= BasicSalleryAllValues.Sub_Timing
AND QMonth_Key_choice.CalcPeriod
= BasicSalleryAllValues.Timing
AND QMonth_Key_choice.FixedDate
= BasicSalleryAllValues.FixedDate);

The subquery will only return a row where there is a match with the current
row of the outer query, so by using the EXISTS predicate to restrict the
outer query only those rows with matches will be deleted.

When undertaking set operations like this its imperative that you back up the
BasicSalleryAllValues table first of course, until you are absolutely sure
its deleting the correct rows.

Ken Sheridan
Stafford, England

יריב החביב said:
Thank You !

Here is the code,

DELETE BasicSalleryAllValues.*
FROM BasicSalleryAllValues INNER JOIN QMonth_Key_choice ON
(BasicSalleryAllValues.Sub_Timing = QMonth_Key_choice.Timing_calc) AND
(BasicSalleryAllValues.Timing = QMonth_Key_choice.CalcPeriod) AND
(BasicSalleryAllValues.FixedDate = QMonth_Key_choice.FixedDate);
The error means that ACCESS cannot identify the unique records that you want
to delete.
Click to expand...
[quoted text clipped - 17 lines]
.
Click to expand...
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
Back
Top