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.
Author |
Topic |
eugz
Posting Yak Master
210 Posts |
Posted - 2013-03-01 : 10:41:20
|
Hi AllI 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 isselect *from dbo.Table2where 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
|
DELETEfrom dbo.Table2where Req_Id not in (select Req_Id from dbo.Table1)CheersMIK |
|
|
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 Table1DELETE 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. |
|
|
|
|
|