delete query from select query inner join problem

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

Right click the design window, then Query Type --> Delete Query

error message...no semicolon at end of statement ?

But I see it?

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" 

Select allOpen in new window

cannot delete from specified table error again?

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"

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.

mbizup...tried that first...did not work.

Cap,
Still getting "Specify the table you want to delete from"

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 ?

fordraiders,

did you copy and paste the query i posted in a new query?

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

Select allOpen in new window

<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"

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.)

Thanks...