sql delete question

I don't know if this is where I should post or not. I am trying to figure out how to get this select statement which generates all of the records I need to delete. Here is the select statement.

SELECT *

FROM SrcComputer as c join SrcDriverEntry as d on

c.DriverEntryId = d.DriverEntryId join SrcFileList as f

on d.FileListId = f.FileListId

where c.compid = 567721765

I tried writing the Delete statement like this, but lo luck.

DELETE

FROM SrcComputer as c join SrcDriverEntry as d on

c.DriverEntryId = d.DriverEntryId join SrcFileList as f

on d.FileListId = f.FileListId

where c.compid = 567721765

How would I re-write the statement to work with joins

Try this

DELETE c

FROM SrcComputer as c join SrcDriverEntry as d on

c.DriverEntryId = d.DriverEntryId join SrcFileList as f

on d.FileListId = f.FileListId

where c.compid = 567721765

I don't have forign key's because I have a many to many relationship inside of my database.  And the peice that I have to use as a primary key of sorts is not unique.  So I can not make it a primary key.  There are 3 tables the first one has a one to many relationship so it has a primary key in it.  The second table has very similar data in it where most entries are the same except one or two different items.  That table links me two a final table which is the same way as the second so I cannot have forign keys link from table 2 to 3 so while I can have a foreign key that links from the first two the second I can not get it to the third.  I just don't understand why my Select statement can give me all of the records I want to delete but there is no delete comand to do the same thing   Why is it so hard to delete the same things a select statement generates   Is there a way to delete all of the results of a select statement

P.S. - I am also kind of new to sql if something is wrong with my assumptions or some things I say please let me know.

The direct answer to your question is that all SQL statements (in all SQL implementations, not just SQL Server) are limited to modifying a single table. So an INSERT inserts rows into one table, an UPDATE updates rows in one table, a DELETE deletes rows from one table.

So you need to either cascade as suggested by another post, or you need to hold the row selectors for each table in a temporary table and then delete. The problem is that in order for the data modification to be transactional you can not just start deleting rows in a table without affecting other tables - in your exampleif the statement deleted a row in one table then the results of your SELECT are immediately different, so rows in the other tables would no longer be in the result-set and wouldn't be deleted anyway!

So, what you could do is something like this (I leave the details to you)

Run your SELECT with the INTO #temptable clause - you can remove any columns from the select list other than those used to identify rows in the tables you want to delete from. Then write three separate DELETEs that delete from the three tables joining to the #temptable to get the right rows. Do all of this inside a transaction (BEGIN TRAN ... COMMIT/ROLLBACK) so that you can rollback if there is a problem, and also to make sure that no-one else uses the rows once the deletes start.

Ok thanks for that I guess I just wasn't looking at it that way. I will see if I can do something with my tables to be able to cascade delete.

Ok I put this in my SqlDataSource DeleteCommand="DELETE FROM SrcComputer WHERE (CompId = @CompId)" How do I set the CompId when they click the Delete link

I added the delete link by going into the gridview and selecting edit columns I then went to CommandField and expanded it and selected the Delete Link

my datasource looks like this.

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:srcConnectionString1 %>"

SelectCommand="SELECT [CompId], [Description], [CompName], [OS], [UserName], [DriverEntryId] FROM [SrcComputer] WHERE ([UserName] = @UserName)" OnSelecting="SqlDataSource2_Selecting" DeleteCommand="DELETE FROM SrcComputer WHERE (CompId = @CompId)">

<SelectParameters>

<asp:SessionParameter Name="UserName" SessionField="UserName" Type="String" />

</SelectParameters>

<DeleteParameters>

<asp:Parameter Name="CompId" />

</DeleteParameters>

</asp:SqlDataSource>

when do I update the session variable