[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

Delete query

hi, i have some table's on my database that is like this:

table01:
id int
fatherid int
caption varchar(50)

this fatherid contains the id of one registry on database, and so on, so i have like a tree one registry connected to others by this fatherid, but here is the deal, i need to delete one registry that has some others connected to this one that i need to delete. example:

house01
        house098
              house097
        house02
house03

here is the deal, i need to delete the house01 and all other registry that are inside, the problem is the house097 has no direct connection to the house01 because the fatherid of it is connected with house098.

how can i accomplish this task, delete the house01 and all other registrys on the tree that are connected with it?
0
rafaelrgl
Asked:
rafaelrgl
1 Solution
 
David ToddSenior DBACommented:
Hi,

Look up the use of CTE's
http://technet.microsoft.com/en-us/library/ms186243.aspx

Otherwise I'll need a table structure and some sample data to give a better answer.

Cheers
  David
0
 
sarabhaiCommented:
With tree1 (id, fatherid )
AS
(
SELECT id, fatherid  FROM tree Where fatherid = 0
UNION ALL
SELECT tree.id, tree.fatherid  FROM tree INNER JOIN tree1 on tree1.id = tree.fatherid

)
SELECT id, fatherid FROM tree1

--pass the fatherid which you want then under it all records are select similarly after confirm delete u can use.
0
 
rafaelrglAuthor Commented:
here we go:

table01( id as int, caption as varchar(50)

table02(id as int, idfather as int, idson as int)

so we have 2 tables, on is just the information and the other is the links between the rows. like

table01:

id         caption

1         flowers
2         trees
3         cars
4         automobiles
5         red flowers

table02:

id       idfather        idson

1        2                  1
2        1                  5
3        4                  3

so we need to delete the row with id = 2 on table01, but we also will need to delete all others that are related with it, like the id = 1 and id=5 on table01, and also delete the reference on table02, like we will not need the id = 1 and id = 2 on table02
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
rafaelrglAuthor Commented:
we have a tree like this:

trees
     flowers
           red flowers
automobiles
     cars
0
 
lofCommented:
Here is a working example that creates sample table, populates it with data and then deletes a hierarchy by it's root (at any level) id. It will work on both 2005 and 2008 version.

If you are using 2008 you may try to use hierarchical data structures
create table Table01 (id int, fatherid int, caption varchar(50))
go

insert Table01 values (1, 0, 'plants')
insert Table01 values (2, 0, 'shapes')
insert Table01 values (3, 1, 'trees')
insert Table01 values (4, 1, 'flowers')
insert Table01 values (5, 2, 'circles')
insert Table01 values (6, 2, 'squares')
insert Table01 values (7, 4, 'tulips')

declare @deletionId int;
set @deletionId = 4;

with Hierarchy (id, fatherid, caption)
as
(
	select * from Table01 where id = @deletionId
	union all
	select Table01.* from Table01 
	inner join Hierarchy on Table01.fatherid = Hierarchy.id
)
delete original 
from Table01 as original
inner join Hierarchy on original.id = Hierarchy.id

Open in new window

0
 
rafaelrglAuthor Commented:
here is one more detail, i have a third table that contains some informations linked with table01, so here we have:

table03( id as int, table01_ID as int, Album as varchar(50))

so i have to delete 3 things,

records on table03, table02 that are related with table01 and also the records that are on the levels below the one we select.

trees
     flowers
           red flowers
automobiles
     cars

so if we chouse to delete the flowers, we will delete those records that are on table03 that are related with red flowers and flowers, and delete the records on table02 that make the conections between the flowers and red flowers. and finally delete the records on table01 redflowers and flowers.

??

any ideas?

0
 
lofCommented:
as always there is few options to choose from

you may have foreign keys with ON DELETE CASCADE option
you may have FOR DELETE triggers
you may first delete data from table03 joining it with the CTE I drafted for you, than the same for table02 and finally, in the same transaction, from the original table.

you could even select indexes from CTE into temporary table and then remove data with those indexes from all three tables (that's a sample code I attached)

there is plenty of possible solutions.


with Hierarchy (id, fatherid, caption)
as
(
        select * from Table01 where id = @deletionId
        union all
        select Table01.* from Table01 
        inner join Hierarchy on Table01.fatherid = Hierarchy.id
)
select primaryKeyColumn
into #tmpTable
from Table01 as original
inner join Hierarchy on original.id = Hierarchy.id

delete from table03 where primaryKeyColumn in (select * from #tmpTable)
delete from table02 where primaryKeyColumn in (select * from #tmpTable)
delete from table01 where primaryKeyColumn in (select * from #tmpTable)

drop table #tmpTable

Open in new window

0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now