Solved

delete query

Posted on 2011-03-15
8
336 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 125 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 13

Accepted Solution

by:
devlab2012 earned 125 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:ewangoya
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

830 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