delete records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

delete records

Author

Topic

madhulatha_b
Starting Member

22 Posts

Posted - 2009-03-24 : 21:10:08

Hi,

I have a table which contains the data as below

AccountID Type Label precedingAccountid
20002 331 T1 11106
20005 331 T2 20002
20003 331 T3 -11106
20004 331 T4 -20003
20006 331 T5 -20004
20008 331 T6 -20006
20007 331 T7 20004
20011 331 T8 -20005

I have an account with accountid 11106. From Application, if I insert an account before the accountid 11106, that new account will have a record with precedingaccountid -11106 and if I add new account after the accountid 11106, it will have a record with precedingaccountid 11106. The cycle goes on.

Now I need to delete all the records from this table which belongs to accountid 11106. In that case I need to delete all the above records. I cannot fetch the records based on TypeId, because the typeid may have multiple accountids.

Can anybody help me to write query to delete the records.

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-03-25 : 02:12:53

Hi,

I haven't tried this query but i think it would work...
I'm not sure if there would be a more better or shorter query that could have your expected data...

--Step 1, get those with positive values
select AccountID,
[Type],
Label,
precedingAccountid
into #temp_pos
from your_table
where precedingAccountid not like '-%'
--Step 2, get those with negative values
select AccountID,
[Type],
Label,
((precedingAccountid) * (-1)) precedingAccountid
into #temp_neg
from your_table
where precedingAccountid like '-%'
--Step 3, get those positive values that have corresponding negative value
select a.AccountID,
[a.Type],
a.Label
into #for_delete_pos
from #temp_pos a
join #temp_neg b
on(a.precedingAccountid = b.precedingAccountid)
--Step 4, get those negative values that have corresponding positive value
select b.AccountID,
[b.Type],
b.Label
into #for_delete_neg
from #temp_pos a
join #temp_neg b
on(a.precedingAccountid = b.precedingAccountid)
--Step 5, delete the records on the main table based on the keys values
delete from your_table
where ((cast(AccountID as varchar)+cast([Type] as varchar)+cast(Label as varchar))
in (select (cast(AccountID as varchar)+cast([Type] as varchar)+cast(Label as varchar))
from #for_delete_neg))
or ((cast(AccountID as varchar)+cast([Type] as varchar)+cast(Label as varchar))
in (select (cast(AccountID as varchar)+cast([Type] as varchar)+cast(Label as varchar))
from #for_delete_pos))

madhulatha_b
Starting Member

22 Posts

Posted - 2009-03-25 : 18:31:52

Thank you. I will check this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-28 : 04:39:39

i think what you want is a CTE


;WIth CTE(AccountID, Type, Label, precedingAccountid )
AS
(
SELECT AccountID, Type, Label, precedingAccountid 
FROM Table
WHERE ABS(precedingAccountid)= 11106
UNION ALL
SELECT t.AccountID, t.Type, t.Label, t.precedingAccountid 
FROM CTE c
JOIN Table t
ON t.precedingAccountid  = c.AccountID 
)
DELETE t
FROM YourTable t
JOIN CTE c
ON c.AccountID=t.AccountID
OPTION (MAXRECURSION 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources