Delete question - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Delete question

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-10-17 : 05:01:00

Hi, I've got a table with bounce emails (BounceMails) containing 1 column with e-mail adresses.

I should delete these adresses from another table (OptIn) and delete the linked categories (OptInCategories).

The tables are linked by a Guid called OptinEmailID.

I can only solve a part of this, so can anyone assist me....

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2007-10-17 : 05:04:29

first you need to delete from the linked categories table and then from the main table.
It will be better if you can post the structure of your table.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-10-17 : 05:10:46

Yes, I know, that is exactly the part I don't know how to do.

I'll post the relevant columns:

BounceMails
Mails (varchar)

And then

OptIn
OptInID (GUID)
OptInMail (varchar)
And then

OptInCategories
OptinCategoryID (GUID)
OptInID (GUID)
CategoryID (GUID)

You see BounceMails.Mails coresponds to the OptIn.OptInMail column
And OptIn.OptinID corresponds to OptinCategories.OptinID

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-10-17 : 05:33:38

Perhaps if I do it in two steps...
so first:

DELETE FROM OptInCategories
FROM BounceMails INNER JOIN
OptIn ON BounceMails.Mail = OptIn.OptInmail INNER JOIN
OptInCategories ON OptIn.OptInID = OptInCategories.OptInID

AND then

DELETE FROM OptIn
FROM BounceMails INNER JOIN
OptIn ON BounceMails.Mail = OptIn.OptInmail

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2007-10-17 : 05:57:07

you are right you have to do it in 2 steps

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 06:13:46

if you are only going to delete SOME of them (with a WHERE clause) then you should probably put the PKs in a Temp Table and use that as the basis of what you delete - so you can be sure you only delete relevant records from both, excluding something else another user has just added! and put it in a transaction so that you don't get the first part succeed, and the second part fail, leaving some partial-data orphans dangling!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources