Solved

ow to make this query MS-SQL

Posted on 2014-01-28
13
173 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
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 47

Expert Comment

by:Dale Fye (Access MVP)
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 69

Accepted Solution

by:
Scott Pletcher earned 500 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 69

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

777 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