SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Delete when not in
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sg2255551
Constraint Violating Yak Guru

272 Posts

Posted - 06/23/2013 :  04:05:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 06/23/2013 :  07:55:15  Show Profile  Reply with Quote
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

272 Posts

Posted - 06/23/2013 :  09:36:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 06/23/2013 :  11:03:43  Show Profile  Reply with Quote
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

272 Posts

Posted - 06/24/2013 :  00:19:00  Show Profile  Reply with Quote
thanks James

The second piece works fine.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000