locked
Delete command with Innerjoin inside gridview in vb.net RRS feed

  • Question

  • User-1578974752 posted

    Hi

    I have to delete values from 2 table Questiondetail( which have 1 question),then its 3 options will be in QuestOptiodetail

    Questiondetail table (below fields)

    Questionmasterid

    Questiondetailid

    QuestOptiodetail table(below fields)

    QuestDetailID

      When I click delete in the grid view, both QuestionMasterID in question detail and questondetailid in QuestOptiodetail must be deleted.But error is showing

    DELETE * FROM QuestionDetail INNERJOIN QuestOptiodetail ON QuestionDetail.QuestDetailID= QuestOptiodetail.QuestDetailID WHERE Questiondetail.QuestionMasterID = @Questiondetail.QuestionMasterID AND QuestOptiodetail.QuestDetailID=QuestOptiodetail.QuestDetailID

    Tuesday, August 14, 2018 10:09 AM

Answers

  • User-2146987983 posted

    You would need two different delete statements to delete data from both tables.

    Refer this link to see the discussion: https://stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server

    Yet another link discussing delete with inner join: http://www.tech-recipes.com/rx/56823/delete-update-rows-using-inner-join-sql-server/

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 14, 2018 10:14 AM
  • User-1171043462 posted

    DELETE does not work with JOINS.

    First Delete from Foreign key table and then from Primary Key Table

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 14, 2018 1:14 PM
  • User-369506445 posted

    hi

    you have to change your query to below

    delete from QuestionDetail where Questiondetail.QuestionMasterID = @Questiondetail.QuestionMasterID ; 
    delete from QuestOptiodetail where QuestOptiodetail.QuestDetailID=QuestOptiodetail.QuestDetailID ;

    also you can use Transaction for commit or rollback the delete 

    BEGIN TRANSACTION [Tran1]
    
    BEGIN TRY
    
    delete from QuestionDetail where Questiondetail.QuestionMasterID = @Questiondetail.QuestionMasterID ; 
    delete from QuestOptiodetail where QuestOptiodetail.QuestDetailID=QuestOptiodetail.QuestDetailID ;
    
    
    COMMIT TRANSACTION [Tran1]
    
    END TRY
    BEGIN CATCH
      ROLLBACK TRANSACTION [Tran1]
    END CATCH  
    
    GO

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 14, 2018 1:39 PM

All replies

  • User-2146987983 posted

    You would need two different delete statements to delete data from both tables.

    Refer this link to see the discussion: https://stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server

    Yet another link discussing delete with inner join: http://www.tech-recipes.com/rx/56823/delete-update-rows-using-inner-join-sql-server/

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 14, 2018 10:14 AM
  • User-1171043462 posted

    DELETE does not work with JOINS.

    First Delete from Foreign key table and then from Primary Key Table

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 14, 2018 1:14 PM
  • User-369506445 posted

    hi

    you have to change your query to below

    delete from QuestionDetail where Questiondetail.QuestionMasterID = @Questiondetail.QuestionMasterID ; 
    delete from QuestOptiodetail where QuestOptiodetail.QuestDetailID=QuestOptiodetail.QuestDetailID ;

    also you can use Transaction for commit or rollback the delete 

    BEGIN TRANSACTION [Tran1]
    
    BEGIN TRY
    
    delete from QuestionDetail where Questiondetail.QuestionMasterID = @Questiondetail.QuestionMasterID ; 
    delete from QuestOptiodetail where QuestOptiodetail.QuestDetailID=QuestOptiodetail.QuestDetailID ;
    
    
    COMMIT TRANSACTION [Tran1]
    
    END TRY
    BEGIN CATCH
      ROLLBACK TRANSACTION [Tran1]
    END CATCH  
    
    GO

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 14, 2018 1:39 PM
  • User-1320437544 posted

    I share me_ritz opinion. You will need multiple delete commands.

    Tuesday, August 14, 2018 3:50 PM