• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

delete query

DELETE FROM PS_TL_GRP_FLD_SEC_BKP TL WHERE NOT EXISTS
(SELECT 1 FROM PSRECFIELDALL_BKP PT WHERE PT.RECNAME = TL.RECNAME AND PT.FIELDNAME=TL.FIELDNAME)
When parsing/executing this query am getting an error near TL however if delete is replaced with SELECT the same query works fine.

Thou I doubt the issue could be with the using of aliasing in a delete query Im not sure if we can write it that way.

Please check and let me know.
0
anilkullam
Asked:
anilkullam
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this small change should do:
DELETE TL
  FROM PS_TL_GRP_FLD_SEC_BKP TL 
WHERE NOT EXISTS 
(SELECT 1 FROM PSRECFIELDALL_BKP PT WHERE PT.RECNAME = TL.RECNAME AND PT.FIELDNAME=TL.FIELDNAME)

Open in new window

0
 
devlab2012Commented:
Try this:

DELETE TL FROM PS_TL_GRP_FLD_SEC_BKP TL WHERE NOT EXISTS
(SELECT 1 FROM PSRECFIELDALL_BKP PT WHERE PT.RECNAME = TL.RECNAME AND PT.FIELDNAME=TL.FIELDNAME)
0
 
anilkullamAuthor Commented:
Thanks for the quick response guys...however I would like to get a clarification on the same..why is the extra alisaing required in a delete stmt..
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
devlab2012Commented:
Syntax of delete statement is

DELETE Table1 FROM Table1, Table2..................

It is not only the DELETE, but anywhere if you are using ALIAS, you have to refer the table/column using the ALIAS. Even the select queries are like:

SELECT a.Col1 from Table1 a
0
 
anilkullamAuthor Commented:
I agree but the same query works fine with just select  instead of delete...any ideas please
0
 
Ephraim WangoyaCommented:

DELETE
FROM PS_TL_GRP_FLD_SEC_BKP
WHERE NOT EXISTS
(SELECT 1 FROM PSRECFIELDALL_BKP PT WHERE PT.RECNAME = PS_TL_GRP_FLD_SEC_BKP.RECNAME AND PT.FIELDNAME=PS_TL_GRP_FLD_SEC_BKP.FIELDNAME)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you mean:
SELECT TL.*
  FROM PS_TL_GRP_FLD_SEC_BKP TL WHERE NOT EXISTS
(SELECT 1 FROM PSRECFIELDALL_BKP PT WHERE PT.RECNAME = TL.RECNAME AND PT.FIELDNAME=TL.FIELDNAME)

Open in new window

would also work the same way as the delete.
I don't really see an interest in getting the 2 queries "work" the same way, as basically, they do different things, don't they?
0
 
anilkullamAuthor Commented:
@angelIII: In fact its a delete query, but making it a select query gives me the records that im gonna delete with that query...its not abt working the same way but the diff is wat im talking abt...hope u got my point :P
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now