Solved

delete query

Posted on 2011-03-15
8
334 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 142

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 142

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

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.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

810 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