Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Delete query syntax help

Posted on 2011-03-01
12
Medium Priority
?
293 Views
Last Modified: 2012-06-22
Need help getting this to work

DELETE  clss_tbl_Temp_Oracle_Download.*
FROM TestCritLike, clss_tbl_Temp_Oracle_Download
WHERE (((clss_tbl_Temp_Oracle_Download.PRIME) Like [TestCritLike].[First Prime Like]));
0
Comment
Question by:Keking
[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
12 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35011555
are you getting any error when running the query?

how does the content of field PRIME compared to the field [First Prime Like]

post sample data..

 try this

DELETE  clss_tbl_Temp_Oracle_Download.*
FROM clss_tbl_Temp_Oracle_Download,TestCritLike
WHERE (((clss_tbl_Temp_Oracle_Download.PRIME) Like [TestCritLike].[First Prime Like]));

or


DELETE  clss_tbl_Temp_Oracle_Download.*
FROM clss_tbl_Temp_Oracle_Download,TestCritLike
WHERE Instr([clss_tbl_Temp_Oracle_Download].[PRIME], [TestCritLike].[First Prime Like]) >0


0
 

Author Comment

by:Keking
ID: 35011659
I am getting, could not delete from Specified Tables. I have an append query that works but now I need to delete the same data from the table after I append it.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35011701
try the query i posted.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:Keking
ID: 35011720
I tried both and am still getting the same error.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35011730
can you upload a copy of the db
0
 

Author Comment

by:Keking
ID: 35011745
It is with a SQL back end and I am not able to upload any data.
0
 

Author Comment

by:Keking
ID: 35011757
here is the append that works. i am trying to delete all that appends from the temp table

INSERT INTO TestTable ( [Period Number], [Period Year], [Period Name], COMPANY, [Company Description], DIVISION, [Division Description], BASIS, [Basis Description], PRIME, [Prime Description], SUB, [Subaccount Description], LOB, [Lob Description], PRODUCT, [Product Description], [COST CENTER], [Cost Center Description], AFFILIATE, [Affiliate Description], [RISK INDICATOR], [Risk Description], NETWORK, [Network Description], [SERVICE TYPE], [Serv Type Description], [FACILITY LOCATION], [Facility Description], PTD_Activity, GLCategory, TestCAT )
SELECT clss_tbl_Temp_Oracle_Download.[Period Number], clss_tbl_Temp_Oracle_Download.[Period Year], clss_tbl_Temp_Oracle_Download.[Period Name], clss_tbl_Temp_Oracle_Download.COMPANY, clss_tbl_Temp_Oracle_Download.[Company Description], clss_tbl_Temp_Oracle_Download.DIVISION, clss_tbl_Temp_Oracle_Download.[Division Description], clss_tbl_Temp_Oracle_Download.BASIS, clss_tbl_Temp_Oracle_Download.[Basis Description], clss_tbl_Temp_Oracle_Download.PRIME, clss_tbl_Temp_Oracle_Download.[Prime Description], clss_tbl_Temp_Oracle_Download.SUB, clss_tbl_Temp_Oracle_Download.[Subaccount Description], clss_tbl_Temp_Oracle_Download.LOB, clss_tbl_Temp_Oracle_Download.[Lob Description], clss_tbl_Temp_Oracle_Download.PRODUCT, clss_tbl_Temp_Oracle_Download.[Product Description], clss_tbl_Temp_Oracle_Download.[COST CENTER], clss_tbl_Temp_Oracle_Download.[Cost Center Description], clss_tbl_Temp_Oracle_Download.AFFILIATE, clss_tbl_Temp_Oracle_Download.[Affiliate Description], clss_tbl_Temp_Oracle_Download.[RISK INDICATOR], clss_tbl_Temp_Oracle_Download.[Risk Description], clss_tbl_Temp_Oracle_Download.NETWORK, clss_tbl_Temp_Oracle_Download.[Network Description], clss_tbl_Temp_Oracle_Download.[SERVICE TYPE], clss_tbl_Temp_Oracle_Download.[Serv Type Description], clss_tbl_Temp_Oracle_Download.[FACILITY LOCATION], clss_tbl_Temp_Oracle_Download.[Facility Description], clss_tbl_Temp_Oracle_Download.PTD_Activity, clss_tbl_Temp_Oracle_Download.GLCategory, IIf([PRIME] Like [First Prime LIke] And [Second Facility Location]=[FACILITY LOCATION],[GLCategory True],[GLCategory False]) AS TestCAT
FROM clss_tbl_Temp_Oracle_Download, TestCritLike
WHERE (((clss_tbl_Temp_Oracle_Download.PRIME) Like [First Prime Like])) OR (((clss_tbl_Temp_Oracle_Download.[SERVICE TYPE]) Like [First Service Type Criterea]));
0
 

Author Comment

by:Keking
ID: 35012417
I think it is because there is a primary key field that is included with the .* and it cannot delete that field. Any ideas how I can get around that?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35012532
Keking,
not sure about oracle, but in sql

you can do this using vba codes

dim dSql as string
dSql="delete *"
dsql =dsql & " FROM clss_tbl_Temp_Oracle_Download,TestCritLike"
dsql =dsql & " WHERE (((clss_tbl_Temp_Oracle_Download.PRIME) Like [TestCritLike].[First Prime Like]))"

currentdb.execute dsql, dbseechanges
0
 

Author Comment

by:Keking
ID: 35016451
That data is from Oracle but it is put into a SQL table. Putting the vba in did not work either. I get incorrect syntax near * . If I take it out I get a problem with the ,
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35016850
Keking,
click on Request Attention above and ask that the Oracle topic be added to this thread, to get more response from oracle experts
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 35022948
give a try.
DELETE FROM clss_tbl_temp_oracle_download 
WHERE       EXISTS (SELECT 1 
                    FROM   testcritlike 
                    WHERE  clss_tbl_temp_oracle_download.prime LIKE [testcritlike].[first prime like]);

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

660 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