Solved

delete query from select query inner join problem

Posted on 2013-11-12
14
528 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ACESS 2010 Query Criteria 23 57
CISCO WIFI 6 73
MS Access vba Print  PDF First page only 16 35
Reoccurring Access Query 24 47
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

752 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