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 |
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2013-06-23 : 04:05:19
|
hiI 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? ThanksCreate Proc [dbo].[TableA_Delete]@ItemID uniqueidentifierasBeginDelete 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 hiI 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? ThanksCreate Proc [dbo].[TableA_Delete]@ItemID uniqueidentifierasBeginDelete 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? |
|
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2013-06-23 : 09:36:46
|
hiThe 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 |
|
|
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 = ItemIDend Or perhapsdelete afrom dbo.TableA awhere a.ItemID = @ItemID and not exists (select * from TableB b where b.ItemID = a.ItemID); |
|
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2013-06-24 : 00:19:00
|
thanks JamesThe second piece works fine. |
|
|
|
|
|