Solved

delete query from select query inner join problem

Posted on 2013-11-12
14
515 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

828 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