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 2008 Forums
 Transact-SQL (2008)
 Delete when not in

Author  Topic 

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2013-06-23 : 04:05:19
hi

I have this stored proc that seems to be deleting all itemid from tableA not found in tableB when it suppose to delete only one.

How should rewrite this sp? Thanks

Create Proc [dbo].[TableA_Delete]
@ItemID uniqueidentifier
as
Begin
Delete from [dbo].[TableA]
WHERE @ItemID not in (select ItemID from [dbo].[TableB]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-23 : 07:55:15
quote:
Originally posted by sg2255551

hi

I have this stored proc that seems to be deleting all itemid from tableA not found in tableB when it suppose to delete only one.

How should rewrite this sp? Thanks

Create Proc [dbo].[TableA_Delete]
@ItemID uniqueidentifier
as
Begin
Delete from [dbo].[TableA]
WHERE @ItemID not in (select ItemID from [dbo].[TableB]

What is the logic you are trying to implement? If it is to delete row(s) from TableA where ItemId = @ItemId, then do the following:
DELETE FROM dbo.TableA WHERE ItemID = @ItemID
But then, you wouldn't need TableB at all. What is the purpose of TableB in your query?
Go to Top of Page

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2013-06-23 : 09:36:46
hi

The purpose is to delete those ItemID from tableA not found in TableB.

In other words, once an ITemID from tableA is being used or reference by tableB, the delete would fail.

I have an ASPNET page which list items in a Gridview that has a Delete link button which calls the stored proc. Inorder to preserve the data integrity, ItemID which is being reference as a FK in tableB, the delete fails. Sometimes a user may create an extra item which they would like to be able delete the item in the gridview.

Thanks
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-23 : 11:03:43
I still didn't get what you are trying to do, so these may or may not be right.
if not exists (select * from dbo.TableB where @ItemId = ItemID)
begin
delete from dbo.TableA where @ItemID = ItemID
end


Or perhaps
delete a
from
dbo.TableA a
where
a.ItemID = @ItemID
and not exists (select * from TableB b where b.ItemID = a.ItemID);
Go to Top of Page

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2013-06-24 : 00:19:00
thanks James

The second piece works fine.
Go to Top of Page
   

- Advertisement -