Delete multiple items from a table with a constraint in another table.

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Delete multiple items from a table with a constraint in another table.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-18 : 09:23:11

Sonny writes "I have 2 tables: univ_dept and univ_course
univ_dept has a list of departments (such as accounting) and univ_course has a list of courses (such as 101 or accoutning 101). I need to delete the duplicate depts in univ_dept but first I need to update univ_course so I can delete from univ_dept because of a constraint between the 2 tables and because if I deleted a dept from univ_course that course would not be available any more and I need to keep the coureses.
The statements below is what I do from query analyzer, it works but takes forever because I have to do it one dept at a time.

1. First I run a select statement so I can see the dups in univ_dept.

select * from univ_dept where dflt_bookstore_id = 5166 order by dept_name

Get a result set like the one below, there are more fields in the table than this but these are the important fields.
In the univ_course table the courses are set by dflt_bookstore_id to identify the bookstore and the dept_id to identify the dept the course is for.

dflt_bookstore_id dept_id campus_id dept_name dept_abrev
    5166            535      13        ACC
    5166            619      13        ACC
    5166            703      13        ACC 
    5166            536      13        AEE
    5166            620      13        AEE
    5166            704      13        AEE
    5166            537      13        AMS
    5166            621      13        AMS
    5166            705      13        AMS
    5166            538      13        ANT
    5166            622      13        ANT
    5166            706      13        ANT

3. Then I run this update statement to replace the dept_id to one of the dept_id's from the dept_table.

update univ_course
set dept_id = 535
where dflt_bookstore_id = 5166
and dept_id in (619,703)

This will update all the courses with a dept_id of 619 and 703 with the dept_id of 535. It does not matter which one I keep as long as I keep one.

4. Now I can delete the depts with an id of 619 and 703 from univ_dept, getting rid of the dups for ACC with this statenent:

delete from univ_dept where dflt_bookstore_id = 5166 and dept_id in (619,703).

Noe I do the next dept. All of this is run from query analyzer and can take several hours to do on one bookstore. I know there is a better way but I am not an sql database person and we don't have one here. Please help and Thank You in advance."

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-02-18 : 19:17:52

Sonny,

I have an answer to your question, but if you are not a SQL person the cure may be worse than the disease. For something like this, I typically

1. create a backup copy of the data
2. lock the tables at the highest setting
3. within a transaction, delete the dupes and update all the tables as required

All of this may be dangerous if you're not confident about SQL. You definitely don't want to accidentally delete your table without a backup.

If you want to read up on this, borrow a copy of "Inside SQL Server" from Microsoft Press (either 7.0 or 2000 is fine). They've got several pages devoted to deleting duplicates.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources