Sql Delete Question

Sql Delete Question

• Jorge Martin

  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

  
  

• Grant Holliday

  That just delete's the SrcComputer entry. I need to get rid of all the records from the joins also. I need the entries from SrcDriverEntry and SrcFileList deleted as well.
  

• ignitionflip

  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't 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't 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.

  
  

• woodland30033

  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't 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.

  
  

• DiegoMC

  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.

  
  

• Jawad Naeem

  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

  
  

• creaturita

  The easiest way to accomplish this is to make sure that your Foreign Keys have CASCADE DELETE set for the 'child' tables. Then when you DELETE from the 'parent' table, the 'child' tables will automatically DELETE also.
  
  
• Sql Delete Question