February 3, 2012 at 9:02 am
I have tow tables
1.Contact(FirstName,MiddleName,LastName.ContactId)
2.Sales(ContactId,TotalDue)
Sales table is referred by contact table with foreign key reference contactid
Contact has duplicate firstnames.Want to delete duplicate firstnames in the contact table and at the same time want to delete all the related data from the sales table.
Please send me sample query for this.
February 3, 2012 at 9:07 am
So you want to delete sales records if they belong to a contact that has a duplicate first name? Wouldn't you want to update the sales record?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 3, 2012 at 9:15 am
Why would you delete a contact just because he has the same first name as another contact? If you insist on doing this, create your foreign key constraint with a CASCADE option.
John
February 3, 2012 at 9:19 am
If this is some type of homework or test, you need to make an attempt at the query. We are happy to help, but not do the work for you.
Delete sales first, joining to contracts.
Then delete the duplicates.
Also, how do you know which duplicate to keep? First name is a strange duplicate to worry about.
February 3, 2012 at 9:55 am
I suggest to create a stag table Sales_new, just a copy of sales, fks and indexes included.
Write a select where you join contact with itself to retrieve top (or min) id grouping by fisrt name for each contact.
join the result with sales and insert in sales_new just using the top id for each contact.
rename sales to sales_old, deactivate the FKS then delete all from contacts is not in the top ids from first query.
finally rename sales_new to sales.
After a time, if no problens arise, make a backup and drops sales_old.
Obs: You can keep a stag table for contacts before the delete as well.
Sometimes its good to keep these deprecated data in a table just in case. It depends of your strategy for backup, downtime, et.
After all is done you can create a unique constraint over first name to ensure it ill be keept correct.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply