Solved

ow to make this query MS-SQL

Posted on 2014-01-28
13
159 Views
Last Modified: 2014-02-05
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
Comment
Question by:team2005
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
Comment Utility
DELETE FROM ControlTable
WHERE ParentID IS NULL
0
 

Expert Comment

by:Duke_George
Comment Utility
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
 
LVL 2

Author Comment

by:team2005
Comment Utility
Hi!

This is only exaple Duke...

I want to remove from my example
ControlID=4,5,6,7
0
 

Expert Comment

by:Duke_George
Comment Utility
then you would use the following:

Delete from ControlTable where ControlID IN(4,5,6,7)
0
 
LVL 34

Expert Comment

by:Brian Crowe
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Author Comment

by:team2005
Comment Utility
Hi!

Duke -> Its an example, next time ther ControlID can be
f.eks 1000299,100288 ...
0
 
LVL 2

Author Comment

by:team2005
Comment Utility
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
 
LVL 34

Expert Comment

by:Brian Crowe
Comment Utility
Then it sounds like you're back to my original post...

DELETE FROM ControlTable
WHERE ParentID IS NULL
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
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
 
LVL 31

Expert Comment

by:awking00
Comment Utility
>>DELETE FROM ct_parent
FROM dbo.ControlTable ct_parent<<
Shouldn't that just be -
DELETE FROM dbo.ControlTable ct_parent
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Nope -- try a syntax check on each one :-).

Or review the DELETE syntax in Books Online.
0
 
LVL 2

Author Closing Comment

by:team2005
Comment Utility
Thanks
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now