delete when relationship exist

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

delete when relationship exist

Author

Topic

salmonraju
Yak Posting Veteran

54 Posts

Posted - 2006-11-02 : 07:25:26

i created two tables Parent ,Child
parent table
ID Salary primary key is ID
1 2000
2 3000
3 4000
4 4000
5 4000

Child Table foreign key is id
id Dept
1 20
2 15
3 20

now i want a query to delete all rows in parent table IF NO RELATIONS EXISTS
Ex: delete from Parent Where id<6

My desired output is
2 rows deleted (i.e, 4,5)

but for this query error Message is generated.How to make work this query so that it may delete 4th and 5th rows

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-02 : 07:28:17

delete p
from parent p
left join child c on c.id = p.id
where c.id is null

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources