I have a CTE created and I am trying to delete tables using the following. Note that CTE expressions work fine but IF ELSE
statements does not.
IF
with FinalSales (time, terminal_id, count) as
(
select time, terminal_id, count(*)
from Final_Sales
group by time, terminal_id
having count(*) = 1 -- condition here
)
delete Sales
from FinalSales
inner join Sales
on Sales.[time] = FinalSales.[time]
and Sales.terminal_id = FinalSales.terminal_id
ELSE IF
with FinalSales (time, terminal_id, count) as
(
select time, terminal_id, count(*)
from Final_Sales
group by time, terminal_id
having count(*) > 1 -- Condition here
)
delete Sales
from FinalSales
inner join Sales
on Sales.[time] = FinalSales.[time]
and Sales.terminal_id = FinalSales.terminal_id
delete #temp1
from FinalSales
inner join #temp1
on #temp1.[time] = FinalSales.[time]
and #temp1.terminal_id = FinalSales.terminal_id
In the first IF
statement, when count(*) = 1
then I delete rows in one table, and then in ELSEIF
statement when count > 1
I want to delete rows in two tables. However, my approach does not work.
How can I wrap it into a IF ELSE
statement? OR is there any other ways to do this?
Whole query in simple structure,
if count(*) == 1:
delete Sales
else if count(*) > 1:
delete Sales, #temp1
something like this. How can I do it in t-sql?
Any ideas would be appreciated.
Aucun commentaire:
Enregistrer un commentaire