Delete records that aren't linked anymore 1

[chrissie1]

Is there a simple way to delete the records in a table that aren't linked (via FK) to another table.

Before you ask. This is for my testdatabase and it's a table that is used by several other tables as a lookup table.

So nothing important just something that could make my life more easy, since I'm very lazy as everybody already knows by now.

Christiaan Baes
Belgium

"My old site" - Me

 

[SQLDenis]

something like this
delete table1
where not exists (select * from table2 where pk=table1.fk)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[RiverGuy]

DELETE FROM MyTable WHERE ForeignKeyField NOT IN (SELECT PrimaryKey FROM PrimaryKeyTable)

 

[gmmastros]

Is this what you are looking for?

[COLOR=blue]Declare[/color] @Table1 [COLOR=blue]Table[/color](Id [COLOR=blue]Int[/color])
[COLOR=blue]Declare[/color] @Table2 [COLOR=blue]Table[/color](Id [COLOR=blue]Int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Table1 [COLOR=blue]Values[/color](1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Table1 [COLOR=blue]Values[/color](2)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Table1 [COLOR=blue]Values[/color](3)

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Table2 [COLOR=blue]Values[/color](1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Table2 [COLOR=blue]Values[/color](3)

[COLOR=blue]Delete[/color] Table1
[COLOR=blue]From[/color]   @Table1 Table1 
       [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] @Table2 Table2
         [COLOR=blue]On[/color] Table1.Id = Table2.Id
[COLOR=blue]Where[/color]  Table2.Id [COLOR=blue]Is[/color] NULL

[COLOR=blue]Select[/color] * [COLOR=blue]From[/color] @Table1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[chrissie1]

Thanks guys, life can be so simple.

Glad to see you still visit RG, I hope you do it more often you are missed in the vb.net forum.

Christiaan Baes
Belgium

"My old site" - Me