By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 896 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Delete Query question

P: 76
The following select query gives me the set of records I want to delete:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblCaseNumbers.*
  2. FROM (tblCaseNumberDump INNER JOIN [dbo_Final Action] ON tblCaseNumberDump.SSN = [dbo_Final Action].[Primary SS#]) INNER JOIN tblCaseNumbers ON [dbo_Final Action].[Master ID] = tblCaseNumbers.ProspectKey
  3. WHERE (([IDENT]=IIf(Right([ACCT],1)='D','D',IIf(Right([ACCT],1)='A','PLP', IIf(Right([ACCT],1)='B','SLP','M')))));
  4.  
When I try to make it a delete query however:
Expand|Select|Wrap|Line Numbers
  1. DELETE tblCaseNumbers.*
  2. FROM (tblCaseNumberDump INNER JOIN [dbo_Final Action] ON tblCaseNumberDump.SSN = [dbo_Final Action].[Primary SS#]) INNER JOIN tblCaseNumbers ON [dbo_Final Action].[Master ID] = tblCaseNumbers.ProspectKey
  3. WHERE (((tblCaseNumbers.Ident)=IIf(Right([ACCT],1)='D','D', IIf(Right([ACCT],1)='A','PLP',IIf(Right([ACCT],1)='B','SLP','M')))));
  4.  
The delete won't work. I am assuming that it is because of the join? Unfortunately, I cannot change the table structure here. All relationships must remain intact. Can anyone help me on this? I would prefer not to do it with ADODB recordsets....

I am writing a series of queries here. The idea is to take data from a spreadsheet (tblCaseNumberDump) and put it in a usable format in tblCaseNumber. tblCaseNumberDump does not have the foreign key in it, so I am using the SSN as an identifier (it is unique in that table). In tblCaseNumbers, SSN is not unique. The key is a combination of the ProspectKey and Ident. The point of this delete query would be to delete any values in tblCaseNumbers where new data is coming.

Thanks,
Josh
Sep 18 '07 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
What table is [ACCT] in? Try the following BTW, reordering the tables may solve the problem.

Expand|Select|Wrap|Line Numbers
  1. DELETE tblCaseNumbers.*
  2. FROM tblCaseNumbers INNER JOIN (tblCaseNumberDump INNER JOIN [dbo_Final Action]
  3. ON tblCaseNumberDump.SSN = [dbo_Final Action].[Primary SS#])
  4. ON tblCaseNumbers.ProspectKey=[dbo_Final Action].[Master ID])
  5. WHERE tblCaseNumbers.Ident=
  6. IIf(Right([ACCT],1)='D','D', 
  7. IIf(Right([ACCT],1)='A','PLP',
  8. IIf(Right([ACCT],1)='B','SLP','M')));
  9.  
Sep 18 '07 #2

P: 76
ACCT is in the tblCaseNumberDump table. I tried your query below and got the same error message "could not delete from specified tables"

Thanks.

What table is [ACCT] in? Try the following BTW, reordering the tables may solve the problem.

Expand|Select|Wrap|Line Numbers
  1. DELETE tblCaseNumbers.*
  2. FROM tblCaseNumbers INNER JOIN (tblCaseNumberDump INNER JOIN [dbo_Final Action]
  3. ON tblCaseNumberDump.SSN = [dbo_Final Action].[Primary SS#])
  4. ON tblCaseNumbers.ProspectKey=[dbo_Final Action].[Master ID])
  5. WHERE tblCaseNumbers.Ident=
  6. IIf(Right([ACCT],1)='D','D', 
  7. IIf(Right([ACCT],1)='A','PLP',
  8. IIf(Right([ACCT],1)='B','SLP','M')));
  9.  
Sep 20 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
OK this is getting a little creative. Back up your table first in case it all goes wrong.

Expand|Select|Wrap|Line Numbers
  1. DELETE tblCaseNumbers.*
  2. FROM tblCaseNumbers 
  3. WHERE tblCaseNumbers.Ident=
  4. IIf(Right([ACCT],1)='D','D', 
  5. IIf(Right([ACCT],1)='A','PLP',
  6. IIf(Right([ACCT],1)='B','SLP','M')))
  7. AND tblCaseNumbers.ProspectKey=DLookup("[Master ID]","dbo_Final Action","[Primary SS#]=tblCaseNumberDump.SSN");
  8.  
Sep 21 '07 #4

Post your reply

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