deleting rows from one table when they exist in another

A project I'm working on has a daily update of a table and then reapplies
each day a table of updates called EditHistory.
What's the best way to do this while avoiding duplicate rows.
I want to copy all rows from the table that's been updated, then remove any
records that just came in that are also in EditHistory and then copy the
ones from EditHistory in. This way I'll be left with a combination of the
records that were never edited along with the ones that have been edited but
the ones have been changed would be gone because the ones from EditHistory
override them.
Thanks for your help.Hi Bob
The better option would be not to insert existing rows
UPDATE m
SET x = O.x, y=O.y
FROM MyTable m
JOIN MyOtherTable O ON m.PK = O.PK
INSERT MyTable ( PK, x , y )
SELECT O.PK, O.x, O.y
FROM myOtherTable O
LEFT JOIN MyTable m ON m.PK = O.PK
WHERE m.PK IS NULL
John
"bob" wrote:

> A project I'm working on has a daily update of a table and then reapplies
> each day a table of updates called EditHistory.
> What's the best way to do this while avoiding duplicate rows.
> I want to copy all rows from the table that's been updated, then remove an
y
> records that just came in that are also in EditHistory and then copy the
> ones from EditHistory in. This way I'll be left with a combination of the
> records that were never edited along with the ones that have been edited b
ut
> the ones have been changed would be gone because the ones from EditHistory
> override them.
> Thanks for your help.
>
>sql

deleting rows from one table when they exist in another

A project I'm working on has a daily update of a table and then reapplies
each day a table of updates called EditHistory.
What's the best way to do this while avoiding duplicate rows.
I want to copy all rows from the table that's been updated, then remove any
records that just came in that are also in EditHistory and then copy the
ones from EditHistory in. This way I'll be left with a combination of the
records that were never edited along with the ones that have been edited but
the ones have been changed would be gone because the ones from EditHistory
override them.
Thanks for your help.Hi Bob
The better option would be not to insert existing rows
UPDATE m
SET x = O.x, y=O.y
FROM MyTable m
JOIN MyOtherTable O ON m.PK = O.PK
INSERT MyTable ( PK, x , y )
SELECT O.PK, O.x, O.y
FROM myOtherTable O
LEFT JOIN MyTable m ON m.PK = O.PK
WHERE m.PK IS NULL
John
"bob" wrote:
> A project I'm working on has a daily update of a table and then reapplies
> each day a table of updates called EditHistory.
> What's the best way to do this while avoiding duplicate rows.
> I want to copy all rows from the table that's been updated, then remove any
> records that just came in that are also in EditHistory and then copy the
> ones from EditHistory in. This way I'll be left with a combination of the
> records that were never edited along with the ones that have been edited but
> the ones have been changed would be gone because the ones from EditHistory
> override them.
> Thanks for your help.
>
>

Deleting from a table

Im working on a project that import data into a gash db. there are about 15
tables in this db and each phase of testing requires the tables to be
cleared. This seems to take ages when i got run a query as follows
delete table1
delete table2
etc
any suggestionon speeding this up ?use
truncate table table1
Use this.. if you don't need the data again. Its faster because its not
logged.
Hope this helps.
--
"Peter Newman" wrote:

> Im working on a project that import data into a gash db. there are about 1
5
> tables in this db and each phase of testing requires the tables to be
> cleared. This seems to take ages when i got run a query as follows
> delete table1
> delete table2
> etc
> any suggestionon speeding this up ?|||You might consider using TRUNCATE TABLE instead. This statement generally
uses few locks and less log space.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:59687486-40A1-43C0-BBBA-1068519772F2@.microsoft.com...
> Im working on a project that import data into a gash db. there are about
> 15
> tables in this db and each phase of testing requires the tables to be
> cleared. This seems to take ages when i got run a query as follows
> delete table1
> delete table2
> etc
> any suggestionon speeding this up ?

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!