delete rows from table - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

delete rows from table

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2013-03-01 : 10:41:20

Hi All

I have two tables. The Table1 has Req_Id like primery key and Table2 has Req_Id like foreign key. And I would like delete rows from Table2 where same Req_Id doesn't exists in Table1. I create SELECT to retrive such records. My select is


select *
from dbo.Table2
where Req_Id not in (select Req_Id from dbo.Table1)

How to delete those records?

Thanks.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-01 : 10:58:24

DELETE
from dbo.Table2
where Req_Id not in (select Req_Id from dbo.Table1)

Cheers
MIK

eugz
Posting Yak Master

210 Posts

Posted - 2013-03-01 : 12:07:55

Hi MIK. Thanks for replay.

How to modify your code if I would like delete just one record? For instance, if Table2 has Req_Id=2 and Table1 doesn't has Req_Id=2.

Thanks.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-01 : 12:19:31

If you know the ID you want to delete, you don't need to involve Table1

DELETE FROM TABLE2 WHERE Req_ID = 2

If you want to select the qualifying rows and delete just one of those, use this:

;WITH cte AS
(
	SELECT TOP (1) * FROM dbo.Table2 t2
	WHERE NOT EXISTS 
	(
		SELECT 1 FROM dbo.Table1 t1 WHERE t1.Req_Id = t2.Req_id
	)
) DELETE FROM cte;

I am assuming that logically there should be a foreign key relationship, but there is no such constraint in the database. If there was, this situation should not have come about in the first place.

eugz
Posting Yak Master

210 Posts

Posted - 2013-03-01 : 14:24:12

Hi James. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources