469,300 Members | 2,203 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,300 developers. It's quick & easy.

Error from Delete query with inner join

Below is the query that I am attempting to run:

DELETE DISTINCTROW Equipment.*
FROM CHSRRequestInProgressB INNER JOIN Equipment ON CHSRRequestInProgressB.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
Jan 6 '11 #1
35 4815
Rabbit
12,516 Expert Mod 8TB
Why is DISTINCTROW in there?
Jan 6 '11 #2
Per the above, If the table CHSRRequestInProgressB 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 CHSRRequestInProgressB 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" & [EquipmentRequested].[CHSR] & "-" & [EquipmentRequested].[InitiatedDate]

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

I hope this helps in getting the answer.
Jan 6 '11 #3
I have attempted with the DISTINCTROW and without it. It gives the same error message.
Jan 6 '11 #4
Rabbit
12,516 Expert Mod 8TB
That doesn't answer my question about why DISTINCTROW is in there. By necessity, DISTINCTROW makes a query non-editable.
Jan 6 '11 #5
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.
Jan 6 '11 #6
dsatino
393 256MB
Try this:

Expand|Select|Wrap|Line Numbers
  1. DELETE e.*
  2. FROM Equipment e
  3. WHERE e.[EquipJ-tag#] IN(SELECT chs.JTag FROM CHSRRequestInProgressB chs)
  4.  
**Before you run this, note that I'm assuming you want to delete any record from the equipment table that appears in chsrequest table.
Jan 6 '11 #7
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
Jan 6 '11 #8
dsatino
393 256MB
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 CHSRRequestInProgressB chs GROUP BY chs.JTag)
Jan 6 '11 #9
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.
Jan 6 '11 #10
dsatino
393 256MB
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 CHSRRequestInProgressB.JTag FROM CHSRRequestInProgressB GROUP BY CHSRRequestInProgressB.JTag)
Jan 6 '11 #11
This gives the same aggregate function error. I believe it is the "Group By" statement.
The table is an access table.
Jan 6 '11 #12
I have also attempted the below statement:

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

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."
Jan 6 '11 #13
dsatino
393 256MB
There shouldn't be an '=' in there.

Switch you query to SQL view and post back what it has int there.
Jan 6 '11 #14
Rabbit
12,516 Expert Mod 8TB
There must be something else going on, can you attach your database?
Jan 6 '11 #15
I cannot seem to attach a txt file or a zip file. I receive an error stating a security token was missing.
Jan 6 '11 #16
Rabbit
12,516 Expert Mod 8TB
Try logging out and then logging back in.
Jan 6 '11 #17
I have logged off and back on. I even closed the browser and reopened. I still receive the same error stating "Your submission could not be processed because a security token was missing".
Jan 6 '11 #18
Rabbit
12,516 Expert Mod 8TB
Alright, we'll have to make do with descriptions.

Can you give us your relevant table definitions, i.e. column names and column types. And your relevant query definitions.
Jan 6 '11 #19
Below are the two tables used for the queries:

Equipment
EquipJ-tag# Text
StatusID Number
CabJ-tag# Text
EquipLoc Text
Manufacturer Text
Model Text
SerNum Text
NumCords Number
Floor Text
Notes Text
Updated Date/Time
PlanID Number
HLAmps Number

EquipmentRequested
RequestedBy Text
WorkPhone Text
MocRoc Text
HPCostCtr Text
WorkType Text
Building Text
Location Number
CHSRLOC Number
PanelID Text
SINumber Text
RIDNumber Text
CHSRNumber Number
CHSRLetter Text
Model Number
Manufacturer Number
Customer Text
AssocSystem Text
ProjectName Text
InitiatedDate Date/Time
RequestedStartDate Date/Time
RequestedCompleteDate Date/Time
ActualCompleteDate Date/Time
ConnectorType Text
NumberOfCords Number
BreakerAmps Number
NumberOfPoles Number
Voltage Number
Notes Text
Notes2 Text
Pre-InstallNotes Text
Status Text
CompletedBy Text
OrigCHSRNumber Number
Overtime Yes/No
AuditDate Date/Time
AuditComplete Yes/No
InstallRDCLoc Text
Phase Text
UPS Text
BreakerType Text
HLAmps Number

Below is the query to select the cancelled EquipmentRequested records:

SELECT "CHSR" & [EquipmentRequested].[CHSRNumber] & "-" & [EquipmentRequested].[InitiatedDate] AS JTag
FROM EquipmentRequested
WHERE (((EquipmentRequested.Status)="CANC"));

Below is the query to delete the records from the EquipmentTable:

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

Below is a sample of an EquipmentRequested record in a comma delimited format:

RequestedBy,WorkPhone,MocRoc,HPCostCtr,WorkType,Bu ilding,Location,CHSRLOC,PanelID,SINumber,RIDNumber ,CHSRNumber,CHSRLetter,Model,Manufacturer,Customer ,AssocSystem,ProjectName,InitiatedDate,RequestedSt artDate,RequestedCompleteDate,ActualCompleteDate,C onnectorType,NumberOfCords,BreakerAmps,NumberOfPol es,Voltage,Notes,Notes2,Pre-InstallNotes,Status,CompletedBy,OrigCHSRNumber,Ove rtime,AuditDate,AuditComplete,InstallRDCLoc,Phase, UPS,BreakerType,HLAmps
Glenn Goodale,939-5662,,US7045895,Electric Install,C2,19542,19542,4233,,,45949,Z,1104,128,,SA BRE,,1/3/2011,12/30/2010,1/14/2011,1/5/2011,H2720,,30,3,208,30A/250V 3 PHASE UPS 6,,,CANC,Unk,,FALSE,,FALSE,C2.M.46,ABC,6,GE,6

Below is a sample of an Equipment record in a comma delimited format:

EquipJ-tag#,StatusID,CabJ-tag#,EquipLoc,Manufacturer,Model,SerNum,NumCords,F loor,Notes,Updated,PlanID,HLAmps,s_GUID,s_ColLinea ge,s_Generation,s_Lineage
CHSR45948-1/4/2011,9,,C2.J41.2,HP/COMPAQ,C7000,,0,C2,,1/4/2011,,6, ,,17791,

Please let me know if I can supply any other information that would help.
Jan 6 '11 #20
On the above sample of an Equipment Record, change the CHSR45948-1/4/2011 to CHSR45949-1/5/2011 so both records will match. I accidentally send one record off from the Equipment table.
Jan 6 '11 #21
I made an error in the date for the above Equipment record, it should read CHSR45949-1/3/2011. I think I need to pay closer attention.

Thanks
Jan 6 '11 #22
Rabbit
12,516 Expert Mod 8TB
Your EquipJ-tag# is CHSR45949-1/3/2011 but when you rebuild your JTag using "CHSR" & [EquipmentRequested].[CHSRNumber] & "-" & [EquipmentRequested].[InitiatedDate], you're going to get CHSR45949-01/03/2011 because the date is returned in the format 01/03/2011 and not 1/3/2011 and so they don't match.
Jan 6 '11 #23
I have the query result on the screen and it is:

CHSR45949-1/3/2011

This was copied from the query. There are no leading zero's in the date format.
Jan 6 '11 #24
Also note that if the delete query is run in Datasheet view, the record is found and listed.
Jan 6 '11 #25
Rabbit
12,516 Expert Mod 8TB
What version of Access are you using? Mine shows leading zeroes.
Jan 6 '11 #26
I forced the date to be 01032011 in the query and changed the table to reflect CHSR45949-01032011 and I still am not able to delete the record. The record is still not updateable. I will change this back to the original format of CHSR45949-1/3/2011.
Jan 6 '11 #27
I have Microsoft Access 2010.
Jan 6 '11 #28
Rabbit
12,516 Expert Mod 8TB
Are you sure this doesn't work?

Expand|Select|Wrap|Line Numbers
  1. DELETE *
  2. FROM Equipment
  3. WHERE (((Equipment.[EquipJ-tag#]) In (SELECT JTag FROM CHSRRequestInProgressB)));
  4.  
Because it works when I try it.
Jan 6 '11 #29
The query listed above is different than any others that were sent. This one works. I really appreciate all of your time and assistance.
Jan 6 '11 #30
Rabbit
12,516 Expert Mod 8TB
In essence it's the same as dsantino's suggestion but it is syntactically different.
Jan 6 '11 #31
In Dsantino's the parenthesis were the onle real differences that I notice once the tags "e" and "chs" are removed:

Dsantino's where statement:
WHERE Equipment.[EquipJ-tag#] IN(SELECT JTag FROM CHSRRequestInProgressB)

Your where statement:
WHERE (((Equipment.[EquipJ-tag#]) In (SELECT JTag FROM CHSRRequestInProgressB)));

I do appreciate both of your assistance.

I don't know why something so simple has to become so complicated.
Jan 6 '11 #32
Rabbit
12,516 Expert Mod 8TB
Not a problem, good luck.

In the end, the parentheses really don't make a difference. It was probably the e and chs that was screwing it up. The syntax he was using is used in SQL Server and possibly Oracle. However, Access doesn't understand what that means. It would have to be AS e and AS chs for that to work in Access.
Jan 6 '11 #33
Actually, his programming works. That was when I stated the query was running after 20 minutes. Once I used the full tables, yours also does the same. There are 29,051 records in the Equipment table and 47,318 records in the Equipment Requested table. Do you have any suggestions to make this run faster?
Jan 6 '11 #34
Rabbit
12,516 Expert Mod 8TB
Put indexes on EquipJ-tag#, CHSRNumber, InitiatedDate, and Status.
Jan 6 '11 #35
You are amazing.....

Thanks
Jan 6 '11 #36

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Mike Wiseley | last post: by
1 post views Thread by Simon Harris | last post: by
4 posts views Thread by Nathan | last post: by
3 posts views Thread by Anila | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.