• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

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 ?
0
team2005
Asked:
team2005
  • 4
  • 3
  • 2
  • +3
1 Solution
 
Brian CroweDatabase AdministratorCommented:
DELETE FROM ControlTable
WHERE ParentID IS NULL
0
 
Duke_GeorgeCommented:
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)
0
 
Dale FyeCommented:
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?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
team2005Author Commented:
Hi!

This is only exaple Duke...

I want to remove from my example
ControlID=4,5,6,7
0
 
Duke_GeorgeCommented:
then you would use the following:

Delete from ControlTable where ControlID IN(4,5,6,7)
0
 
Brian CroweDatabase AdministratorCommented:
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
0
 
team2005Author Commented:
Hi!

Duke -> Its an example, next time ther ControlID can be
f.eks 1000299,100288 ...
0
 
team2005Author Commented:
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
0
 
Brian CroweDatabase AdministratorCommented:
Then it sounds like you're back to my original post...

DELETE FROM ControlTable
WHERE ParentID IS NULL
0
 
Scott PletcherSenior DBACommented:
DELETE FROM ct_parent
FROM dbo.ControlTable ct_parent
WHERE
    ct_parent.ParentID IS NULL AND
    NOT EXISTS(
        SELECT 1
        FROM dbo.ControlTable ct_child
        WHERE
            ct_child.ParentID = ct_parent.ControlID
        )
0
 
awking00Commented:
>>DELETE FROM ct_parent
FROM dbo.ControlTable ct_parent<<
Shouldn't that just be -
DELETE FROM dbo.ControlTable ct_parent
0
 
Scott PletcherSenior DBACommented:
Nope -- try a syntax check on each one :-).

Or review the DELETE syntax in Books Online.
0
 
team2005Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now