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!

deleting data from database

Ive just started learning ASP and i am using MySQL databases. So far ive managed to make a newsposting thing that allows me to insert news and read them. Now i want to have the option of deleting data from the database directly from the site, but whatever i try to write in the sql command it doesn't want to work. I don't know where i should ask this question but ill start here =)
ive read several guides but some of them are PHP guides or ASP but msaccess databases.
this is how my stuff looks at the moment:

(page name: newsremove.asp)
<%
if request("delid") <> "" then
delid=request("delid")
sql="DELETE * FROM news WHERE ID=" & delid
conn.execute(sql)
end if
%>
<form action="newsremove.asp" method="post">
Type in the ID of the post you wish to remove: <input type="text" name="delid" size=20><br>
<input type="submit" value="remove this post!">
</form>

I also have this little part where i show all the news but i don't think it has anything to do with the errors i get.

do until rs.eof
response.write "<hr align=left width=300 size=2 color=black noshading>"
response.write rs("time") & " by " & rs("poster") & ". ID: " & rs("id")
rs.moveNext
loop

The errors i get when i try to remove a entry is
"ADODB.Connection.1 (0x80004005)
SQLState: 42000 Native Error Code: 1064 [TCX][MyODBC]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM news WHERE ID=9' at line 1
/newsremove.asp, line 25"

ANY HELP AT ALL IS VERY THX!the error message points to exactly where the problem is

remove the asterisk :)

DELETE FROM news WHERE ID=937|||:O what!? i dont need the *? im gona try and see =)|||well, now i get my old friend "expected 'end' "

"Expected 'End'
/newsremove.asp, line 31, column 40"

Line 31 is the empty line between "if request("delid") = "" then" and "delid=request("delid")"

<form action="newsremove.asp" method="post">
Type in the ID of the post you wish to remove: <input type="text" name="delid" size=20><br>
<b>Make sure its the right one! Once you delete a post it can't be recovered!!</b><br>
<input type="submit" value="Im sure, remove this post!">
</form>
<%

if request("delid") = "" then

delid=request("delid")
sql="DELETE FROM news WHERE ID="& delid""
set rs=conn.execute(sql)

end if


sql="SELECT * FROM news ORDER BY tid DESC"
set rs=conn.execute(sql)

do until rs.eof
response.write "<hr align=left width=300 size=2 color=black noshading>"
response.write rs("TID") & " by " & rs("POSTER") & ". ID: " & rs("ID")
rs.moveNext
loop
%>

I get this friggin error all the time!! i know the answer is that i am missing a "end if" statement but i aint!! Please fast help is appreciated!|||i don't code in that particular scripting language, but it sure looks like you are trying to delete based on an id that isn't there

if request("delid") = ""
then
delid=request("delid")
sql="DELETE FROM news WHERE ID="& delid""|||hmm sounds very reasonable now that you mention it=) ill try it tomorrow now i gotta go to bed

Delete-operation performance problem

Hi!

I'm experiencing the following experience problem with my SQL Server 2000.

Explanation a' la example;

1.
I insert data using my SP; EXEC dbo.up_DataInsert
This is fine, SQL Profiler duration only a few ms.

2.
I try to select the data.
select * from dbo.tblData where DataNumber = 283279
This is fine, SQL Profiler duration only a few ms.

3.
delete from dbo.tblData where DataNumber = 283279
This is NOT fine, SQL Profiler duration up to 50 seconds!!!

I have got a unique index with ignore duplicate key and the table has about
180.000 records.

What could be wrong here??

/Magnus"Magnus sterberg" <magnus.osterberg@.abo.fi> wrote in message
news:d9bgug$rcr$1@.plaza.suomi.net...
> Hi!
> I'm experiencing the following experience problem with my SQL Server 2000.
> Explanation a' la example;
> 1.
> I insert data using my SP; EXEC dbo.up_DataInsert
> This is fine, SQL Profiler duration only a few ms.
> 2.
> I try to select the data.
> select * from dbo.tblData where DataNumber = 283279
> This is fine, SQL Profiler duration only a few ms.
> 3.
> delete from dbo.tblData where DataNumber = 283279
> This is NOT fine, SQL Profiler duration up to 50 seconds!!!
> I have got a unique index with ignore duplicate key and the table has
> about 180.000 records.
> What could be wrong here??
> /Magnus

No idea - have you checked the query plan in Profiler to see where the time
is going? And are there any other factors, such as a DELETE trigger on the
table? If you can post the table DDL (including keys and indexes) and also
details of the query plan (SET SHOWPLAN_TEXT/ALL), someone may be able to
suggest something.

Simon|||Magnus sterberg (magnus.osterberg@.abo.fi) writes:
> I'm experiencing the following experience problem with my SQL Server 2000.
> Explanation a' la example;
> 1.
> I insert data using my SP; EXEC dbo.up_DataInsert
> This is fine, SQL Profiler duration only a few ms.
> 2.
> I try to select the data.
> select * from dbo.tblData where DataNumber = 283279
> This is fine, SQL Profiler duration only a few ms.
> 3.
> delete from dbo.tblData where DataNumber = 283279
> This is NOT fine, SQL Profiler duration up to 50 seconds!!!
> I have got a unique index with ignore duplicate key and the table has
> about 180.000 records.

Possible causes:

1) Blocking.

2) Autogrow.

3) There is a trigger on the table.

4) There is a FK constraint from another, big table, and the FK
column in that table is not indexed.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Deleteing large bulks of data

Brief background:

We are using SQL Server 2000, and one of the tables stores user
sessions details (each time our users logs into our system we insert a
new record in the session table, and each time user logs out from our
system we insert another record in the same table).
SESSION_ID is the primary key and it is clustered index.
The system produces 5 million session records/day.

The problem:

Each day we transfer the session data (delta only) to other machine and
we want to delete bulk of ~5 million sessions. This should happend
without any interfering of our customers activity ( in the same time,
we should not block the table - new sessions should be created).

What is the best way to perform such task ?generally truncate table xxx ...will be MUCH faster than delete. Be
aware of some of the logging issues associated with truncate table
before you do this. Search your BOL for "truncate table".

MJKulangara
http://sqladventures.blogspot.com|||Truncating table will delete the entire table, and this is not what we
want. We are looing for a method to delete specific sessions (by
specifying the exact sessions ID's).|||rosherman@.hotmail.com (rosherman@.hotmail.com) writes:
> We are using SQL Server 2000, and one of the tables stores user
> sessions details (each time our users logs into our system we insert a
> new record in the session table, and each time user logs out from our
> system we insert another record in the same table).
> SESSION_ID is the primary key and it is clustered index.
> The system produces 5 million session records/day.
> The problem:
> Each day we transfer the session data (delta only) to other machine and
> we want to delete bulk of ~5 million sessions. This should happend
> without any interfering of our customers activity ( in the same time,
> we should not block the table - new sessions should be created).
> What is the best way to perform such task ?

If I understand this correctly, you want to delete the main bulk of the
five million rows, but keep some of them.

I would consider doing something like:

1) Rename the table.
2) Create a new table with the same schema.
3) Insert the rows you want to keep from the old table to the new table.
4) Drop the old table.

You would need to put 1) and 2) into a transaction. During this
transactions logins would be blocked, but it would be a matter of
centiseconds.

If you can find a method to define a clean cut a head, then you could
consider partitioned views. That is, you would have a set of table
that are united in a view, and a CHECK constraint defining which
intervals that go into which table. Insertions would be into the view.
You would transfer one table a time, and then truncate and finally
redefine it to fit another slot in the future.

--
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|||Thanks. We'll try this method.

delete triggers

sql2k sp3
I assumed this would work as easily as the Insert and Update Triggers I
wrote did:
create trigger DelAuthors
on authors
for delete
as
begin
set nocount on
delete tmp_authors
where tmp_authors.au_id = deleted.au_id
end
Server: Msg 107, Level 16, State 2, Procedure DelAuthors, Line 7
The column prefix 'deleted' does not match with a table name or alias name
used in the query.
Heres the schema for tmp_authors(and Authors):
CREATE TABLE [dbo].[tmp_authors] (
[au_id] [id] NOT NULL ,
[au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contract] [bit] NOT NULL
) ON [PRIMARY]
GO
Help is appreciated.
TIA, ChrisRChange your DELETE statement to:
DELETE FROM tmp_authors
WHERE EXISTS
(SELECT *
FROM deleted
WHERE tmp_authors.au_id = deleted.au_id)
That's the ANSI Standard update syntax. SQL Server also supports Microsoft
own proprietary version of the DELETE statement using joins and which,
bizarely, requires an extra FROM clause to do what you are attempting.
DELETE
FROM tmp_authors
FROM deleted
WHERE tmp_authors.au_id = deleted.au_id
David Portas
SQL Server MVP
--|||Thanks David.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ZLWdnU8W65uGbv_fRVn-tw@.giganews.com...
> Change your DELETE statement to:
> DELETE FROM tmp_authors
> WHERE EXISTS
> (SELECT *
> FROM deleted
> WHERE tmp_authors.au_id = deleted.au_id)
> That's the ANSI Standard update syntax. SQL Server also supports Microsoft
> own proprietary version of the DELETE statement using joins and which,
> bizarely, requires an extra FROM clause to do what you are attempting.
> DELETE
> FROM tmp_authors
> FROM deleted
> WHERE tmp_authors.au_id = deleted.au_id
> --
> David Portas
> SQL Server MVP
> --
>

DELETE TOP

Environment: SQL Server 2005
Language: T-SQL

I want to insert the top first record from the temp table '#NewTemp'
to the Employee table 1 record at a time until all the records from the temp table
have been copied.
After inserting the top first record into the Employee table, how can I retrive the identity key 'NewEmployeeID' from the Employee table?
I would then like to delete the top row in the #NewTemp table.
The process should be repeated until the @.Counter variable is 0.
Currently, I get an error msg 'Incorrect syntax near the keyword TOP'.What's wrong with the following T-SQL and how can I fix it? thanks.

BEGIN
DECLARE @.Counter as INT
SELECT @.Counter = COUNT(*) FROM #NewTemp
DECLARE @.TopCount AS INT
SET @.TopCount = 1

WHILE @.Counter > 0
BEGIN
INSERT INTO dbo.Employee(
EmployeeFName
, EmployeeLName
, EmployeePhone
, EmployeeEmail)
SELECT TOP(@.TopCount)
EmployeeFName
, EmployeeLName
, EmployeePhone
, EmployeeEmail
FROM dbo.#NewTemp

SELECT SCOPE_IDENTITY() AS NewEmployeeID

PRINT 'The count is ' + CONVERT (VARCHAR (10), @.Counter)

PRINT --NewEmployeeID

SET @.Counter = @.Counter -1

DELETE TOP(@.TopCount) from dbo.#NewTemp
END

DROP TABLE dbo.#NewTemp
END

I'm sure you have your reasons, but I have to say, this is very, very 'odd' indeed.

DELETE does NOT recognize TOP. You cannot [ DELETE TOP ]. In fact, if users are active in your database, a second query using TOP may not return the same row(s) as the first query. In your SELECT query, TOP without a WHERE criteria just doesn't make any sense.

Why go to so much trouble?

Let us know what you are attempting to accomplish and perhaps we can help you.

|||

What I'm trying to accomplish is this.

Copy each row from temp table to the Employee table. After inserting each record into the Employee table,

I would like to get the Identity key for the new record inserted from the Employee table. Thanks.

|||

You will need to use the new OUTPUT clause of the input statement. You cannot use either @.@.identity or scope_identity() when you are inserting multiple rows. Just a second and I will try to get you an example. Give a look to this article:

http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx

You ought to be able to do something like this to retrieve your list of identities:

create table #identityList (idKey integer)

insert into targetTable
select col1,
col2,
...
colN
output inserted.theIdentityColumn
into #identityList
from yourTempTable

-- to retrieve the identity values:
select idKey

from #identityList

Now, if you are going to need to match your identies back to the temp table that you inserted from you will also need to include column(s) in your identity list that make the inserted row unique.

|||

thanks, nice article. I think it will help. Is 'inserted' a reserved word in the code above?

In my code above, reason I have while loop and a counter is so I can go each record at time for insertion from the

temp table to the Employee table. What would be the alternative if using scope_identity() or @.@.identity?

|||

John,

In my earlier response, I asked if you would give us more detail information about what you are attempting to accomplish. It is not at all clear what is going on here.

If you only wanted to move the data from the #NewTemp table into the Employees table, it is as simple as an INSERT... SELECT.

Code Snippet

INSERT INTO dbo.Employee
( EmployeeFName,
EmployeeLName,
EmployeePhone,
EmployeeEmail
)
SELECT
EmployeeFName,
EmployeeLName,
EmployeePhone,
EmployeeEmail
FROM dbo.#NewTemp

However, perhaps there is some thing you still haven't told us. What is your concern about the SCOPE_IDENTITY() or @.@.IDENTITY?

We can't do a good job of helping you unless you give us all of the information.

|||

create table dbo.#newTemp
( EmployeeFName varchar(20),
EmployeeLName varchar(20),
EmployeePhone varchar(14),
EmployeeEmail varchar(30)
)
insert into dbo.#newTemp
select 'Dave', 'Mugambo', '(800)555-1212', null union all
select 'Kent', 'Waldrop', '(800)555-1212', null union all
select 'Geoff', 'Waldmeyer', '(800)555-1212', null
select * from dbo.#newTemp
/*
EmployeeFName EmployeeLName EmployeePhone EmployeeEmail
-- -- --
Dave Mugambo (800)555-1212 NULL
Kent Waldrop (800)555-1212 NULL
Geoff Waldmeyer (800)555-1212 NULL
*/

declare @.idList table (newEmployeeID int)

insert into dbo.Employee
output inserted.employeeId
into @.idList
select EmployeeFName,
EmployeeLname,
EmployeePhone,
EmployeeEmail
from dbo.#newTemp

-- --
-- If you need to see the new employee
-- information, you can you the
-- permanent table to the IDList like
-- this:
-- --

select EmployeeId,
EmployeeFName,
EmployeeLName,
EmployeePhone,
EmployeeEmail
from dbo.employee a
inner join @.idList b
on a.employeeId = b.newEmployeeId

/*
EmployeeId EmployeeFName EmployeeLName EmployeePhone EmployeeEmail
-- -- -
1 Dave Mugambo (800)555-1212 NULL
2 Kent Waldrop (800)555-1212 NULL
3 Geoff Waldmeyer (800)555-1212 NULL
*/

go

drop table dbo.#newTemp

(edited the syntax; it was previously wrong)

|||

After filling the table variable with the inserted records,

how can I get each newEmployeeId one at a time. I would like to

pass this newEmployeeID to another sp one at a time.

thanks much.

|||

If this is what you must do, then use a cursor with a while loop -- but understand, cursors tend to be performance killers. A better approach is to design set based operations; it is what SQL server is best at. I will edit this with some posts that talk about the problems of cursors.

Here are some posts that comment on cursors:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1372104&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=892822&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=447559&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=437891&SiteID=1 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=588762&SiteID=1

I have many more examples in my list.

|||

If you really MUST do a row-wise operation, instead of a CURSOR, I recommend using the previous suggestion of the SQL 2005 OUTPUT keyword, outputing into a table variable, then adding an IDENTITY column to the table variable, and then using a WHILE loop to step through the rows in the table variable.

It will not be as resource intensive on the server, and most likely even execute faster than a CURSOR.

|||

As an aside, delete does recognize TOP in 2005. It is non ordered, so it doesn't make sense in this particular case. It's use is to batch deletes, like if you want to delete a large number of rows, but want to limit the pressure on the log, you can just delete them in batches over and over until all data is gone.

Code Snippet

create table #NewTemp
(
EmployeeFName varchar(30)
, EmployeeLName varchar(30)
, EmployeePhone varchar(30)
, EmployeeEmail varchar(30)
)

insert into #newTemp
select 'fred','flintstone','quarry 3424','fredf@.slate.com'
union
select 'barney','rubble','quarry 3452','barneyr@.slate.com'

delete top (1) from #newTemp

select *
from #newTemp