?
Solved

delete query

Posted on 2011-03-15
8
Medium Priority
?
345 Views
Last Modified: 2012-05-11
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
Comment
Question by:anilkullam
[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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35138190
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
 
LVL 13

Expert Comment

by:devlab2012
ID: 35138201
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
 

Author Comment

by:anilkullam
ID: 35138319
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 13

Accepted Solution

by:
devlab2012 earned 500 total points
ID: 35138527
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
 

Author Closing Comment

by:anilkullam
ID: 35138613
I agree but the same query works fine with just select  instead of delete...any ideas please
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35138621

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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35138642
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
 

Author Comment

by:anilkullam
ID: 35139511
@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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

770 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