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.
| Author |
Topic |
|
sqlconfused
Yak Posting Veteran
50 Posts |
 Posted - 2012-09-23 : 10:19:44
|
| Hi.I have two tables - photos and photocommentsPhotocomments has 4 columns, one is called PNUMBRpnumber, etc1, etc2, etc32383, nice pic, Ralph, somedate2387, my holiday, John, somedatePhotos has three columns, one is called ID (identity column) and one is called S_IDID, S_ID, dr2383, s435, 442384, s593, 532385, t555, 332386, y4589, 5642387, s593, 11What I am trying to do is when a person deletes their photo album I need to delete all the photos which I currently do like this and which will remove entries 2384 and 2387 as seen above.Delete from photos where s_id = 's593'Now I'm trying to delete the comments associated with the photos and am wondering if I can combine the delete with a JOIN so that I can (as an example):Delete from photos where s_id = 's593' AND delete from photocomments where photos.id = photocomments.pnumberCould someone give me the correct syntax for this?The delete must delete from photos based on S_ID and then from photocomments pcolumn column where it is equal to photos.ID |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-23 : 10:31:05
|
| You cannot delete data from two tables in a single delete statement. There are two alternatives:a) if pnumber in PhotoComments table is a foreign key from Photos table, then you can set up a cascade delete (which means when you delete photos, any rows with pnumber equal to the deleted ID's will be automatically deleted from the photocomments table. This page has documention: http://msdn.microsoft.com/en-us/library/ms186973(v=sql.105).aspxMany people including yours truly don't like cascade deletes because it deletes rows from tables "under the table". But, theoretically and practically there is nothing wrong with it.b) Simply have two delete statements. If you like you can wrap them in a transaction and try-catch block so they both succeed together or fail together. |
 |
|
|
sqlconfused
Yak Posting Veteran
50 Posts |
Posted - 2012-09-23 : 11:00:23
|
| Thanks for the reply. I currently use two delete statements and will probably keep it that way. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-23 : 11:06:41
|
| That should work just fine - you may want to consider wrapping them in a transaction and try-catch block to avoid the possibility that data from one table would get deleted and the data from the other table may remain if something goes wrong between the deletes, or if one table happens to be locked by some other process etc. Example "B. Using TRY…CATCH in a transaction" and "C. Using TRY…CATCH with XACT_STATE" on this page shows you how to do that: http://msdn.microsoft.com/en-us/library/ms175976.aspx |
|
|
|
|
|
|
|
|
Deleting from two tables