Deleting entries under a "Category" that is being deleted??

Hello. I have a simple project using 3 tables: Categories, Subcategories, and Items. I have just gotten my insert, edit, and delete functions to work, but I've noticed a problem I hope someone can help with:

When I delete a certain category (lets say "Restaurants"), the subcategories and items that were in that category still remain in the database/table. (If I delete "Restaurants", the subcategories "Italian", "Seafood", etc - as well as any items in those subcategories - are not deleted).

So what would I need to do to delete any Subcategory that is in the deleted Category (shares a CategoryID) and then delete all Items in those Subcategories?

For reference, all Subcategories in a Category have reference to that CategoryID, and all Items in a Subcategory have a "SubcategoryID" field. I understand that I need to traverse the tables and remove all Subcategories with the CategoryID being deleted, but since the Items do not have a reference to the CategoryID, how would I delete those as well?

Thanks to whoever can help me out here!

First of all, you should add constraints to your tables. They will prevent you from removing Categorys if you have reletad Subcategorys in order to maintain referential integrity.

And then you have 2 options, the first is that in the constraint you declare that in deleting a Category, all releted SubCategorys (and Items) are deleted also.

The other option is that you delete the first all items in the table item that are related to subcategorys that are related to the category that you want to delete, then delete all items in the table subcategory that are related to the category that you want to delete and finally delete the category form the table category:

DELETE FROM item WHERE item.subcategoryID In (SELECT subcategory.subcategoryID FROM category INNER JOIN subcategory ON category.categoryID = subcategory.categoryID WHERE category.categoryID = @.categoryID);

DELETE FROM subcategory WHERE subcategory.category In (SELECT category.categoryID FROM category WHERE category.categoryID = @.categoryID);

DELETE FROM category WHERE category.categoryID = @.categoryID;

|||

Thanks alot for the reply! Actually, a bit after I posted the question I began to do something very similar to what your 2nd option above. What I thought to do was to remove the "Delete" button on the GridView and replace it with a Select Button whose text was "Delete", and then when the selectedIndexChanged event for the GridView is called (meaning when the user clicks the select button (which says "Delete"), I used this statement to delete the "items" in that category:

DELETE FROM items WHERE items.subcategoryID IN (SELECT subcategories.subcategoryID FROM subcategories WHERE subcategories.CategoryID = " & Me.GridView1.SelectedValue

However, when I tried to run this, I got an error since there were no actual Items in the Items table who were children of the selected category to delete. So I began to write a FOR EACH statement to go through the Items table and search for any items who were nested in that category, and if there were any, then call the above command. This seems like alot of work though, and I'm really interested in these "Constraints" you spoke of.

Could you please give more info/examples of how to declare and use a constraint on a delete command such as what I want to do? If possible, I'd like to just have the constraint declare that if a category is deleted, all subcategories and items under that category are also deleted. If this can be done automatically, that would be perfect!

I've never heard of or seen these constraints in action before though, so any help or links you can give will help alot! I'm done with work for the day, so take your time and I will check them out tomorrow! Thanks!

|||

For more information about constraints search the internet, keywords "SQL SERVER FOREIGN KEY CONSTRAINTS", For example:

http://technet.microsoft.com/en-us/library/ms175464.aspx

If you want more information about constraints that will also delete related records in other tables, search for "SQL SERVER CASCADING CONSTRAINTS", for example:

http://technet.microsoft.com/en-us/library/ms186973.aspx

But the error that you got when running the delete statement, is not because there where no records. If there are records they will be deleted, if not, there will be no error. I think that the SQL statement is not correct, I think you forgot to add the ")" and the end!

And when using parameters (in this case the categoryID), it's better to useparameterized Queries!

|||

Ah, yeah I just forgot to type the ) in the post (it wasn't directly copied from the code). I'm not sure what the error pertained to, however I have read up on the Cascading Delete funtion and implemented it, and it works great! I didn't think to look into a solution using SQL Server instead of Visual Studio.

Thanks alot for your help!

Deleted "old" transaction logs = (no items) in EM. How to recover?

A mistake was made and "old" transaction logs (and old backups) were
deleted. It appears that a long living transaction was living in one of
those logs. Now the EP shows (no items). The DBs themselves are still
functioning OK, just the EP shell that's not working.

What's the proper resolution?

Thank you in advance,

FBCK(optimistck@.gmail.com) writes:
> A mistake was made and "old" transaction logs (and old backups) were
> deleted. It appears that a long living transaction was living in one of
> those logs. Now the EP shows (no items). The DBs themselves are still
> functioning OK, just the EP shell that's not working.
> What's the proper resolution?

EP?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Delete matched query

I'm working with a legacy application that stores its data in a SQL Server 2
K
database. I want to write a delete query to delete items from table A, wher
e
there is no match in table B, where the match is based on matches of the two
PK fields.
I tried the following, but got an error: Incorrect syntax near the keyword
'LEFT'
DELETE FROM A
LEFT JOIN B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL
Any help would be greatly appreciated.
DaleHow about this?
DELETE A
FROM A
INNER JOIN B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL|||Try
DELETE A
From A LEFT JOIN B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL
But beware, when you use this syntax, if you uswe Table Aliases, i.e., if
the Tables are actually name dTableA and TableB, then the First line has t
ouse the Alias, not the actual Table Name,. i.e.,
DELETE A
From TableA A Left Join TableB B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL
A Much clearer way to code this is to use SQL that mirrors exactly what you
want
Delete TableA
Where Not Exists
(Select * From TableB
Where PK = TableA.PK)
"Dale Fye" wrote:

> I'm working with a legacy application that stores its data in a SQL Server
2K
> database. I want to write a delete query to delete items from table A, wh
ere
> there is no match in table B, where the match is based on matches of the t
wo
> PK fields.
> I tried the following, but got an error: Incorrect syntax near the keyword
> 'LEFT'
> DELETE FROM A
> LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> Any help would be greatly appreciated.
> Dale|||I meant
Delete TableA
Where Not Exists
(Select * From TableB
Where ExerciseID = TableA.ExerciseID
And UserID = TableA.UserID)
"CBretana" wrote:
> Try
> DELETE A
> From A LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> But beware, when you use this syntax, if you uswe Table Aliases, i.e., if
> the Tables are actually name dTableA and TableB, then the First line has t
> ouse the Alias, not the actual Table Name,. i.e.,
> DELETE A
> From TableA A Left Join TableB B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> A Much clearer way to code this is to use SQL that mirrors exactly what yo
u
> want
> Delete TableA
> Where Not Exists
> (Select * From TableB
> Where PK = TableA.PK)
> "Dale Fye" wrote:
>|||Try this
Delete From A
Where Not Exists(
Select * From B Where B.Col1=A.Col1 And B.Col2=A.Col2
)
Dmitriy
"Dale Fye" <dale.fye@.nospam.com> wrote in message
news:B16B3312-7EDD-4D85-A2D0-7806F2064BEE@.microsoft.com...
> I'm working with a legacy application that stores its data in a SQL Server
2K
> database. I want to write a delete query to delete items from table A,
where
> there is no match in table B, where the match is based on matches of the
two
> PK fields.
> I tried the following, but got an error: Incorrect syntax near the keyword
> 'LEFT'
> DELETE FROM A
> LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> Any help would be greatly appreciated.
> Dale|||Dale Fye wrote:
> I'm working with a legacy application that stores its data in a SQL Server
2K
> database. I want to write a delete query to delete items from table A, wh
ere
> there is no match in table B, where the match is based on matches of the t
wo
> PK fields.
> I tried the following, but got an error: Incorrect syntax near the keyword
> 'LEFT'
> DELETE FROM A
> LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Try this:
DELETE FROM A
WHERE NOT EXISTS (SELECT * FROM B
WHERE B.ExerciseID = A.ExerciseID
AND B.UserID = A.UserID)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjX6lIechKqOuFEgEQIHQQCfe67DnoBuMAKw
lKtCX8+H7Wd9ANAAmwS2
sbyoMnwLgSk2DmyMUtxtgESf
=hYSb
--END PGP SIGNATURE--|||Thanks to all who responded. I've been working in Access so long, I forgot
about Exists and Not Exists.
"CBretana" wrote:
> Try
> DELETE A
> From A LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> But beware, when you use this syntax, if you uswe Table Aliases, i.e., if
> the Tables are actually name dTableA and TableB, then the First line has t
> ouse the Alias, not the actual Table Name,. i.e.,
> DELETE A
> From TableA A Left Join TableB B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> A Much clearer way to code this is to use SQL that mirrors exactly what yo
u
> want
> Delete TableA
> Where Not Exists
> (Select * From TableB
> Where PK = TableA.PK)
> "Dale Fye" wrote:
>