Solved

delete query from select query inner join problem

Posted on 2013-11-12
14
489 Views
Last Modified: 2013-11-15
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
Comment
Question by:fordraiders
  • 6
  • 5
  • 3
14 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39641931
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
 
LVL 61

Expert Comment

by:mbizup
ID: 39641944
Also, using the query builder, design view try this method:

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

Author Comment

by:fordraiders
ID: 39641954
error message...no semicolon at end of statement ?

But I see it?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39641967
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
 
LVL 3

Author Comment

by:fordraiders
ID: 39641969
cannot delete from specified table error again?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39641971
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
 
LVL 61

Expert Comment

by:mbizup
ID: 39641978
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 3

Author Comment

by:fordraiders
ID: 39641989
mbizup...tried that first...did not work.

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

Author Comment

by:fordraiders
ID: 39641996
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39642000
fordraiders,

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

Expert Comment

by:mbizup
ID: 39642005
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39642009
<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
 
LVL 61

Expert Comment

by:mbizup
ID: 39642069
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
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 39651927
Thanks...
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now