Error from Delete query with inner join

[Rick Beach]

Below is the query that I am attempting to run:

DELETE DISTINCTROW Equipment.*
FROM CHSRRequestInPr ogressB INNER JOIN Equipment ON CHSRRequestInPr ogressB.JTag = Equipment.[EquipJ-tag#];

I receive an error stating "Could not delete from specified tables". The table I need the rows deleted from are in the Equipment table. The "[EquipJ-tag#]" is the primary key in this table. I can edit from within the equipment table itself, so the table is not read only. I can run the query in datasheet view and the information is correct, but I can not update the information.

Please assist.

Rick

[Rabbit]

Why is DISTINCTROW in there?

[Rick Beach]

Per the above, If the table CHSRRequestInPr ogressB is removed, the ability to change or delete records is possible. When this query is added as an Inner Join, the query becomes read only. The JTAG in the CHSRRequestInPr ogressB query is a combination of a text field + a data field from table + a data field from table. Below is the exact information for creating the jtag:

JTag: "CHSR" & [EquipmentReques ted].[CHSR] & "-" & [EquipmentReques ted].[InitiatedDate]

I do not understand why the CHSRRequestInPr ogressB would have any impact when I only want to delete from the Equipment table.

I hope this helps in getting the answer.

[Rick Beach]

I have attempted with the DISTINCTROW and without it. It gives the same error message.

[Rabbit]

That doesn't answer my question about why DISTINCTROW is in there. By necessity, DISTINCTROW makes a query non-editable.

[Rick Beach]

As stated, this has also been removed and I still recieve the same error. It was put in place because searching for answers, this needed to be in place if the table being deleted from was not the primary key. In my table the primary key is a combination of the JTAG, StatusID and EquipLoc.

[dsatino]

Try this:

DELETE e.*
FROM Equipment e
WHERE e.[EquipJ-tag#] IN(SELECT chs.JTag FROM CHSRRequestInProgressB chs)

**Before you run this, note that I'm assuming you want to delete any record from the equipment table that appears in chsrequest table.

[Rick Beach]

I am attempting to run the query you stated above. It has been running for 10 minutes and has not completed. You are correct in you assumption listed above.

Thanks

[dsatino]

Usually delete queries only take seconds, or less. My guess is that there are a lot of records in the chs table.
Try this:

DELETE e.*
FROM Equipment e
WHERE e.[EquipJ-tag#] IN(SELECT chs.JTag FROM CHSRRequestInPr ogressB chs GROUP BY chs.JTag)

[Rick Beach]

This gives an error: You tried to execute a querythat does not include the specified expression 'e.[EquipJ-tag#]=' as part of an aggregate function.

[dsatino]

Ok, I guess it doesn't like the alias'. The following removes those. Also, is your table in Access or on a different type of server?


DELETE Equipment.*
FROM Equipment
WHERE Equipment.[EquipJ-tag#] IN(SELECT CHSRRequestInPr ogressB.JTag FROM CHSRRequestInPr ogressB GROUP BY CHSRRequestInPr ogressB.JTag)

[Rick Beach]

This gives the same aggregate function error. I believe it is the "Group By" statement.
The table is an access table.

[Rick Beach]

I have also attempted the below statement:

DELETE Equipment.*
FROM Equipment
WHERE (((Equipment.[EquipJ-tag#])=(SELECT CHSRRequestInPr ogressB.JTag
FROM CHSRRequestInPr ogressB)));

This gives an error stating "At most one record can be returned by this subquery." The help section states "A subquery of this kind cannot return more than one record. Revise the SELECT statement of the subquery to request only one record."

[dsatino]

There shouldn't be an '=' in there.

Switch you query to SQL view and post back what it has int there.

[Rabbit]

There must be something else going on, can you attach your database?