asked on

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?

David Todd

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

Sara bhai

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.

rafaelrgl

ASKER

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

rafaelrgl

ASKER

we have a tree like this:

trees
flowers
red flowers
automobiles
cars

lof

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

rafaelrgl

ASKER

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?

ASKER CERTIFIED SOLUTION

lof

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial