Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 539
  • Last Modified:

delete query from select query inner join problem

access 2010..

I have this select query.

SELECT SapAltData_Td.ITEM, SapAltData_Td.ALT, SapAltData_Td.ALTSHORT
FROM SapAltData_Td INNER JOIN alt_acc_comp_score1 ON (SapAltData_Td.ALT = alt_acc_comp_score1.ALT_ACC_MATERIAL_NO) AND (SapAltData_Td.ITEM = alt_acc_comp_score1.MATERIAL_NO)
WHERE (((alt_acc_comp_score1.LABEL)="O"))
ORDER BY SapAltData_Td.ITEM;

Open in new window


I need to delete the data in "SapAltData_Td"


Thanks
fordraiders
0
Fordraiders
Asked:
Fordraiders
  • 6
  • 5
  • 3
1 Solution
 
mbizupCommented:
Make a backup and try this (deletes entire rows that meet the criteria):

DELETE SapAltData_Td.*
FROM SapAltData_Td INNER JOIN alt_acc_comp_score1 ON (SapAltData_Td.ALT = alt_acc_comp_score1.ALT_ACC_MATERIAL_NO) AND (SapAltData_Td.ITEM = alt_acc_comp_score1.MATERIAL_NO)
WHERE (((alt_acc_comp_score1.LABEL)="O"))
ORDER BY SapAltData_Td.ITEM;

Open in new window

0
 
mbizupCommented:
Also, using the query builder, design view try this method:

Right click the design window, then Query Type --> Delete Query
0
 
FordraidersAuthor Commented:
error message...no semicolon at end of statement ?

But I see it?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mbizupCommented:
Try simplifying it and removing the ORDER BY (not relevant in delete queries)

DELETE SapAltData_Td.*
FROM SapAltData_Td INNER JOIN alt_acc_comp_score1 ON SapAltData_Td.ALT = alt_acc_comp_score1.ALT_ACC_MATERIAL_NO  AND SapAltData_Td.ITEM = alt_acc_comp_score1.MATERIAL_NO
WHERE alt_acc_comp_score1.LABEL ="O" 

Open in new window

0
 
FordraidersAuthor Commented:
cannot delete from specified table error again?
0
 
Rey Obrero (Capricorn1)Commented:
try this


delete *
FROM SapAltData_Td, alt_acc_comp_score1
where (SapAltData_Td.ALT = alt_acc_comp_score1.ALT_ACC_MATERIAL_NO) AND (SapAltData_Td.ITEM = alt_acc_comp_score1.MATERIAL_NO)
And (alt_acc_comp_score1.LABEL)="O"
0
 
mbizupCommented:
If that doesn't work, try the design-view method I suggested in comment http:#a39641944 to convert your SELECT query to a DELETE query.
0
 
FordraidersAuthor Commented:
mbizup...tried that first...did not work.

Cap,
Still getting "Specify the table you want to delete from"
0
 
FordraidersAuthor Commented:
uuug...alt_acc_comp_score1 is  a linked table..
Even though i'am not deleting records from that table..i think thats causing the error ?
0
 
Rey Obrero (Capricorn1)Commented:
fordraiders,

did you copy and paste the query i posted in a new query?
0
 
mbizupCommented:
Interesting - double check table and field names, etc.

This general syntax works for me - tested:

DELETE tblInvoiceMain.*
FROM tblClients INNER JOIN tblInvoiceMain ON tblClients.ClientID = tblInvoiceMain.ClientID
WHERE tblClients.ClientID =1

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
<uuug...alt_acc_comp_score1 is  a linked table..>

it doesn't matter as long as you have the correct name of the table



try this one


delete SapAltData_Td.*
FROM SapAltData_Td, alt_acc_comp_score1
where (SapAltData_Td.ALT = alt_acc_comp_score1.ALT_ACC_MATERIAL_NO) AND (SapAltData_Td.ITEM = alt_acc_comp_score1.MATERIAL_NO)
And (alt_acc_comp_score1.LABEL)="O"
0
 
mbizupCommented:
Double - check all of the posts here in completely new queries.  I just ran a quick test, using linked tables, starting with my post at http:#a39641967 .  

The syntax there is good (and there are several other posts here that should work assuming correct table and field names, your database being properly linked -- try relinking, just to ensure that your table structures as seen in the front-end are current, etc.)
0
 
FordraidersAuthor Commented:
Thanks...
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now