Solved

delete query

Posted on 2011-03-15
8
341 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 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
Industry Leaders: 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!

 
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: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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

690 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