April 12, 2011 at 4:42 am
Hi,
Can I implement delete statement with in case statement? i.e.
Select idbatch =
CASE
when idbatch=43 then
Delete from <...> where flpricestep BETWEEN '80' AND '100' and idinst in
(select idInst from <...> where ...
Is it possible? I tried it but this is what I am getting. Incorrect syntax near the keyword 'Delete'.
Tried googling also but did not get useful info. Please suggest.
Thanks!
Sunny
April 12, 2011 at 5:18 am
DELETE t from table t where 1 = case when <condition> then 1 else 0 end
April 12, 2011 at 9:46 am
Ninja's_RGR'us is absolutely correct (as he always is) in his syntax and use of CASE in a DELETE statement. However, it doesn't address the question of if you should even use a case statement there (I know... that wasn't your question... he answered your specific question extremely well).
Typically, I would just delete based on specific values in the WHERE clause. No need to use conditional statements.
DELETE <tablename>
WHERE <this = that AND these = those... etc>
However, if you need conditions evaluated first, I would think that an IF statement is more appropriate than a CASE statement... in this situation:
IF <this is true and that is true... etc>
DELETE <tablename>
WHERE <this = that AND these = those... etc>
The CASE statement provides conditional evaluation inside of a SQL statement, but it is used to output a single value within a field evaluation, join term, where clause, etc. That is why Ninja's answer had to create a case statement that outputted a value of 1 if condition was true, then the delete statement had a condition where the CASE statement was = 1. The IF statement allows you to envelop the entire operation into a condition, which that doesn't get touched unless the condition evaluates to true. It's a little more traditional (my opinion) than using a CASE in the WHERE.
Unfortunately, you redacted the meaty parts of your statement that would tell me what you are trying to evaluate. If you post your actual code, I may be able to provide a more specific answer.
April 12, 2011 at 10:12 pm
Ok. Let me explain the situation for which I need solution.
I have 2 tables.
1: Pos_testing. Columns: Idinst
2: Pos. Columns: Idinst and idbatch
Now pos_testing has 3 values. 12345,123456,1234567
And pos has 5 values.
Idinst idbatch
12345 41
123456 42
123456 43
23456 44
12345 42
I need to pick the values from pos_testing one by one and want to check it into Pos table. If it is found then need to fetch the corresponding idbatch from pos.
if the idbatch = 41 then
Delete from posriskmatrix_testing where flpricestep BETWEEN '80' AND '100' and idinst in
(select idInst from pos_testing where pos_testing.idInst = pos.idInst and pos_testing.idbatch=41)
I hope this will help. I was thinking of using Cursors but then thought of an alternative.
If you require more info please let me know. Thanks for you help.
Sunny!
April 13, 2011 at 1:32 am
You don't need a cursor.
Run this and check that the output represents the rows you want to delete:
SELECT pmt.*, '#', pt.*, '#', p.*
FROM posriskmatrix_testing pmt
INNER JOIN pos_testing pt
ON pt.idinst = pmt.idinst
INNER JOIN pos p
ON p.idinst = pt.idinst AND p.idbatch = 41
WHERE pmt.flpricestep BETWEEN '80' AND '100'
If the output does represent the rows you want to delete, then change the query to a DELETE:
DELETE pmt
FROM posriskmatrix_testing pmt
INNER JOIN pos_testing pt
ON pt.idinst = pmt.idinst
INNER JOIN pos p
ON p.idinst = pt.idinst AND p.idbatch = 41
WHERE pmt.flpricestep BETWEEN '80' AND '100'
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 18, 2011 at 1:33 am
Thanks for your reply.
I implemented this through Delete statements after checking batch condition with if exists. It worked as expected.
Thanks!!
Sunny
April 18, 2011 at 1:59 am
Hi Sunny
Posting your solution may help others with a similar problem - also, it would allow the community to verify your solution and if necessary make further recommendations.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 18, 2011 at 2:55 am
OK, This is what I have implemented.
If exists (select idbatch from jbjob inner join insts_temp on insts_temp.inst = jbjob.idInst and insts_temp.BusDate = jbjob.BusinessDate and jbjob.idbatch=40)
Begin
Delete from fxPosRisk where idinst in
(select idInst from jbjob where jbjob.idInst = fxPosRisk.idInst and jbjob.idbatch=40 and jbjob.idInst in (select inst from insts_temp where insts_temp.inst = jbjob.idInst and insts_temp.BusDate = jbjob.BusinessDate))
Delete from Posfxsensitivity where idinst in
(select idInst from jbjob where jbjob.idInst = Posfxsensitivity.idInst and jbjob.idbatch=40 and jbjob.idInst in (select inst from insts_temp where insts_temp.inst = jbjob.idInst and insts_temp.BusDate = jbjob.BusinessDate))
Print 'Entries Deleted from fxPosRisk and Posfxsensitivity.'
End
The advantage of having if exists here is it will check for idbatch existence at the start and exit immediately if idbatch does not match. Similar logic for all other idbatches.
Thanks!
Sunny
April 18, 2011 at 3:50 am
sunnymalhotra (4/18/2011)
OK, This is what I have implemented.If exists (select idbatch from jbjob inner join insts_temp on insts_temp.inst = jbjob.idInst and insts_temp.BusDate = jbjob.BusinessDate and jbjob.idbatch=40)
Begin
Delete from fxPosRisk where idinst in
(select idInst from jbjob where jbjob.idInst = fxPosRisk.idInst and jbjob.idbatch=40 and jbjob.idInst in (select inst from insts_temp where insts_temp.inst = jbjob.idInst and insts_temp.BusDate = jbjob.BusinessDate))
Delete from Posfxsensitivity where idinst in
(select idInst from jbjob where jbjob.idInst = Posfxsensitivity.idInst and jbjob.idbatch=40 and jbjob.idInst in (select inst from insts_temp where insts_temp.inst = jbjob.idInst and insts_temp.BusDate = jbjob.BusinessDate))
Print 'Entries Deleted from fxPosRisk and Posfxsensitivity.'
End
The advantage of having if exists here is it will check for idbatch existence at the start and exit immediately if idbatch does not match. Similar logic for all other idbatches.
Thanks!
Sunny
Sunny, you appear to be thoroughly confused between IN and EXISTS. Have a look at Gail Shaw's excellent blog here[/url], it will explain the differences between them.
Secondly, you are fetching idinst from a bunch of tables twice, once for each DELETE. Consider streaming the output of
(select idInst from jbjob where jbjob.idInst = Posfxsensitivity.idInst and jbjob.idbatch=40 and jbjob.idInst in (select inst from insts_temp where insts_temp.inst = jbjob.idInst and insts_temp.BusDate = jbjob.BusinessDate)) into a #temp table. This would be particularly effective if a small number of rows were returned from big tables.
Finally, you can't assume that IN and EXISTS will complete faster than INNER JOIN. Always test, and check the execution plan.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply