team2005
asked on
ow to make this query MS-SQL
Hi!
Have a table that contains:
Tablename -> ControlTable
ControlID Tekst ParentID
1 This is a test Null
2 This are another test 1
3 .................... 1
4 xxxxxxxxxxxxxx Null
5 xxxxxxxxxxxxxx Null
6 xxxxxxxxxxxxxx Null
7 xxxxxxxxxxxxxx Null
8 xxxxxxxxxxxxxx Null
9 xxxxxxxxxxxxxx 8
10 xxxxxxxxxxxxxx 8
t remove
I want to build a delete sql, that remove all records
that dosent have Parents-> ControlID = 4,5,6,7 dosent have parent
and i want to remove this records
How can i do this ?
Have a table that contains:
Tablename -> ControlTable
ControlID Tekst ParentID
1 This is a test Null
2 This are another test 1
3 .................... 1
4 xxxxxxxxxxxxxx Null
5 xxxxxxxxxxxxxx Null
6 xxxxxxxxxxxxxx Null
7 xxxxxxxxxxxxxx Null
8 xxxxxxxxxxxxxx Null
9 xxxxxxxxxxxxxx 8
10 xxxxxxxxxxxxxx 8
t remove
I want to build a delete sql, that remove all records
that dosent have Parents-> ControlID = 4,5,6,7 dosent have parent
and i want to remove this records
How can i do this ?
The following will delete records that do not have a control ID of 4,5,6,7
Delete from ControlTable where ControlID Not IN(4,5,6,7)
Delete from ControlTable where ControlID Not IN(4,5,6,7)
Is this table nested any deeper than what your example shows?
If so, do you want to keep all of the children, grand-children, ... of those 4 records as well as those 4 root nodes?
If so, do you want to keep all of the children, grand-children, ... of those 4 records as well as those 4 root nodes?
ASKER
Hi!
This is only exaple Duke...
I want to remove from my example
ControlID=4,5,6,7
This is only exaple Duke...
I want to remove from my example
ControlID=4,5,6,7
then you would use the following:
Delete from ControlTable where ControlID IN(4,5,6,7)
Delete from ControlTable where ControlID IN(4,5,6,7)
ControlID's 1 & 8 also don't have ParentID values. Did you want to delete them as well?
I want to build a delete sql, that remove all records
that dosent have Parents
ASKER
Hi!
Duke -> Its an example, next time ther ControlID can be
f.eks 1000299,100288 ...
Duke -> Its an example, next time ther ControlID can be
f.eks 1000299,100288 ...
ASKER
Hi!
Try to explane this in onother way...
If ParentID = Null , then this i call Parent Controll
if ParentID <> Null , then its refering to a parent controll
and this we call child controll
I want a sql that removes alle records that are Parent Controll
that have no child controlls
Try to explane this in onother way...
If ParentID = Null , then this i call Parent Controll
if ParentID <> Null , then its refering to a parent controll
and this we call child controll
I want a sql that removes alle records that are Parent Controll
that have no child controlls
Then it sounds like you're back to my original post...
DELETE FROM ControlTable
WHERE ParentID IS NULL
DELETE FROM ControlTable
WHERE ParentID IS NULL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>DELETE FROM ct_parent
FROM dbo.ControlTable ct_parent<<
Shouldn't that just be -
DELETE FROM dbo.ControlTable ct_parent
FROM dbo.ControlTable ct_parent<<
Shouldn't that just be -
DELETE FROM dbo.ControlTable ct_parent
Nope -- try a syntax check on each one :-).
Or review the DELETE syntax in Books Online.
Or review the DELETE syntax in Books Online.
ASKER
Thanks
WHERE ParentID IS NULL