Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ow to make this query MS-SQL

Posted on 2014-01-28
13
Medium Priority
?
200 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39815124
DELETE FROM ControlTable
WHERE ParentID IS NULL
0
 

Expert Comment

by:Duke_George
ID: 39815149
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 48

Expert Comment

by:Dale Fye
ID: 39815159
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:team2005
ID: 39815172
Hi!

This is only exaple Duke...

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

Expert Comment

by:Duke_George
ID: 39815184
then you would use the following:

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

Expert Comment

by:Brian Crowe
ID: 39815192
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
 
LVL 2

Author Comment

by:team2005
ID: 39815241
Hi!

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

Author Comment

by:team2005
ID: 39815262
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
ID: 39815294
Then it sounds like you're back to my original post...

DELETE FROM ControlTable
WHERE ParentID IS NULL
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39815407
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 32

Expert Comment

by:awking00
ID: 39815586
>>DELETE FROM ct_parent
FROM dbo.ControlTable ct_parent<<
Shouldn't that just be -
DELETE FROM dbo.ControlTable ct_parent
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39815735
Nope -- try a syntax check on each one :-).

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

Author Closing Comment

by:team2005
ID: 39835211
Thanks
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

704 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