February 23, 2015 at 10:46 am
Hi All,
I have two tables , one is main table and another one is Duplicate check table. I need to find the duplicates in the DupeCheck table and delete it.
What I don't get is, I am able to find the duplicates in the DupeCheck table but im not able to delete it.
Below is the query I used to find the duplicates in the DupeCheck table.
select * from FinancialChanges_DupCk a where exists ( Select 1 from FinancialChanges b where a.Number = b.Number and a.Fix = b.Fix and a.AmountType = b.AmountType and a.Amount = b.Amount )
Above query correctly fetches the duplicate record in the Dupe Check table .
Now I want to delete that row from DupeCheck table.
I tried below command
delete from FinancialChanges_DupCk a where exists ( Select 1 from FinancialChanges b where a.Number = b.Number and a.Fix = b.Fix and a.AmountType= b.AmountType and a.Amount = b.Amount
but it throws below error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'a'.
I want to delete the records I found using the first query. How do I achieve that ... please advise...
February 23, 2015 at 10:54 am
Try this:
delete a
from FinancialChanges_DupCk a
where exists ( Select 1 from FinancialChanges b where a.Number = b.Number and a.Fix = b.Fix and a.AmountType= b.AmountType and a.Amount = b.Amount
February 23, 2015 at 11:23 am
Hi Luis,
Thanks Very much... Much appreciated... 🙂
I am new to Database world... I heard like SQL queries are not like programming statements which executes one after the other. There is no guarantee that the second query runs only after the first query.
I have to make sure that only after insertion into an audit table, the duplicate records are deleted in the DupeCheck table.
I have the the insert statements and now the delete statement with your help.
I can write a query like below
insert into audittable ......(condition goes here)
delete from DupeChecktable......(Condition goes here)
But there is no guarantee that first insert happens and then only delete happens right... ?
I have to make sure that insert always happens first before deleting ....
Please explain how to achieve this...
February 23, 2015 at 11:52 am
I'm not sure if someone explained it wrong or if you misunderstood what happens in SQL.
SQL (in this case T-SQL) is a declarative language, so you tell the engine what do you need to do and it decides the best way to do it. But those decisions are independent on each statement. In your example, the insert needs to finish so the delete can start.
An example on SQL Server engine deciding what will go first would be with a multi join statement.
SELECT *
FROM TableA a
JOIN TableB b ON a.SomeID = b.SomeID
JOIN TableC c ON b.AnotherID = c.AnotherID
WHERE a.column1 = 45
AND c.columnX = 'Some Value'
In this case, you can't be sure if the engine will join TableA and TableB before joining TableC or if TableB and TableC will be joined before joining TableA. The same way, you can't be sure that the first condition in the WHERE clause will be executed before the second condition. The order of this will be defined by the cost of each action to get the best execution plan available. Of course, there's a lot of information on how this works but I just tried to simplify it.
I'm not sure if I was clear enough.
February 24, 2015 at 1:44 am
passiondba (2/23/2015)
I can write a query like below
insert into audittable ......(condition goes here)delete from DupeChecktable......(Condition goes here)
But there is no guarantee that first insert happens and then only delete happens right... ?.
It is absolutely, 100%, completely guaranteed that the insert will happen first, run to completion and once it has finished the delete will run.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2015 at 12:41 am
Thanks Luis, GilaMonster .... !!!
Understood now !!!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply