Delete Rows - 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 2000 Forums

Transact-SQL (2000)

Delete Rows

Author

Topic

dotnetmonkey
Starting Member

6 Posts

Posted - 2007-05-15 : 07:40:39

I have the following 3 tables to store lists of contacts.

Lists
-----
listID
listName

Contacts
--------
contactID
contactName
contactEmail

ListsContacts
-------------
listID
contactID

The problem I have is when I come to delete a list. Before I delete a list from the Lists table I want to remove any records from the Contacts table that are associated with that list (in the ListsContacts table). I need to write some sql that will delete all records from the Contacts table that are associated with a certain listID in the ListsContacts table.

This sounds like it should be fairly simple but I can not figure out the correct sql.

Can anyone help with this?

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-15 : 07:47:06

[code]Delete c
From Contacts c join ListsContacts lc on c.contactID = lc.contactID
Where lc.ListID = @listID

Delete from ListContacts
Where ListID = @listID[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources