Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

Delete query syntax help

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
Keking
Asked:
Keking
  • 6
  • 5
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
KekingAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
try the query i posted.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
KekingAuthor Commented:
I tried both and am still getting the same error.
0
 
Rey Obrero (Capricorn1)Commented:
can you upload a copy of the db
0
 
KekingAuthor Commented:
It is with a SQL back end and I am not able to upload any data.
0
 
KekingAuthor Commented:
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
 
KekingAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
KekingAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
SharathData EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now