[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 541
  • 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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