locked
Insert/Update Query Not Working RRS feed

  • Question

  • User1122355199 posted

    Hello everyone and thank you for your help in advance.  I am trying to write a query that can be used to insert a record if it does not exist and update the record if it does exist.  I have used this technique successfully in the past, but for some reason, the following query only inserts and fails to update.  The obvious question is whether the record exists or not and I have verified, at least to my knowledge, that the record exists and should be updated.  Here is the sproc:

    CREATE PROCEDURE [dbo].[sp_InsertUpdate_tbl_Log_ReviewofSystems]
    	-- Add the parameters for the stored procedure here
    	@VisitID				int,
    	@MRNumber				int,
    	@EnteredBy				varchar(50),
    	@QuestionID				int,
    	@QuestionResponse		varchar(500)
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	DECLARE @EnglishDescription		varchar(500)
    	DECLARE @Category				varchar(50)
    	DECLARE @CategoryCode			int
    	
    
    	UPDATE tbl_Log_ReviewofSystems SET EnteredBy  = @EnteredBy, QuestionResponse = @QuestionResponse  WHERE QuestionID = @QuestionID AND VisitID = @VisitID
    
    	IF @@ROWCOUNT=0
    
    	SELECT @EnglishDescription = EnglishDescription, @Category = Category, @CategoryCode = CategoryCode FROM tbl_Log_ReviewofSystemsValues WHERE ID = @QuestionID
    
        -- Insert statements for procedure here
    	Insert into tbl_Log_ReviewofSystems (VisitID, MRNumber, EnteredBy, QuestionID, CategoryCode, Category, EnglishDescription, QuestionResponse) Values (@VisitID, @MRNumber, @EnteredBy, @QuestionID, @CategoryCode, @Category, @EnglishDescription, @QuestionResponse)
    
    

    I have tested this multiple times by executing the sproc with the same data, thinking it should initially insert the record and then update the record each subsequent time.   However, the procedure only seems to insert the record multiple times and I can't figure out why.  Any help would be appreciated.

    Wednesday, October 4, 2017 3:04 PM

Answers

  • User452040443 posted

    Hi,

    I think it's missing to delimit the Select and Insert with Begin and End, but I believe it would be better to use the Merge command:

    merge tbl_Log_ReviewofSystems as t
    using 
    (
        select @QuestionID, @VisitID, @MRNumber, @EnteredBy, @QuestionResponse, EnglishDescription, Category, CategoryCode 
        FROM tbl_Log_ReviewofSystemsValues
        WHERE ID = @QuestionID
    
    ) as s (QuestionID, VisitID, MRNumber, EnteredBy, QuestionResponse, EnglishDescription, Category, CategoryCode)
    on s.QuestionID = t.QuestionID AND s.VisitID = t.VisitID
    when matched then
        UPDATE SET EnteredBy = s.EnteredBy, QuestionResponse = s.QuestionResponse
    when not matched then
        Insert (VisitID, MRNumber, EnteredBy, QuestionID, CategoryCode, Category, EnglishDescription, QuestionResponse) 
        Values (s.VisitID, s.MRNumber, s.EnteredBy, s.QuestionID, s.CategoryCode, s.Category, s.EnglishDescription, s.QuestionResponse)

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 4, 2017 6:24 PM

All replies

  • User1122355199 posted

    I also gave this a try, but it did not work:

    CREATE PROCEDURE [dbo].[sp_InsertUpdate_tbl_Log_ReviewofSystems]
    	-- Add the parameters for the stored procedure here
    	@VisitID				int,
    	@MRNumber				int,
    	@EnteredBy				varchar(50),
    	@QuestionID				int,
    	@QuestionResponse		varchar(500)
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	DECLARE @EnglishDescription		varchar(500)
    	DECLARE @Category				varchar(50)
    	DECLARE @CategoryCode			int
    	/*
    	SET @EnglishDescription, @Category, @CategoryCode = (SELECT EnglishDescription, Category, CategoryCode FROM tbl_Log_ReviewofSystemsValues WHERE ID = @QuestionID)
    	*/
    	
    	IF EXISTS (SELECT * FROM tbl_Log_ReviewofSystems WHERE QuestionID = @QuestionID AND VisitID = @VisitID)
    		UPDATE tbl_Log_ReviewofSystems SET EnteredBy  = @EnteredBy, QuestionResponse = @QuestionResponse  WHERE QuestionID = @QuestionID AND VisitID = @VisitID
    	ELSE	
    		SELECT @EnglishDescription = EnglishDescription, @Category = Category, @CategoryCode = CategoryCode FROM tbl_Log_ReviewofSystemsValues WHERE ID = @QuestionID
    
    		-- Insert statements for procedure here
    		Insert into tbl_Log_ReviewofSystems (VisitID, MRNumber, EnteredBy, QuestionID, CategoryCode, Category, EnglishDescription, QuestionResponse) Values (@VisitID, @MRNumber, @EnteredBy, @QuestionID, @CategoryCode, @Category, @EnglishDescription, @QuestionResponse)
    
    END

    Once again, the insert happens each time the procedure is run rather than updating.

    Wednesday, October 4, 2017 5:43 PM
  • User452040443 posted

    Hi,

    I think it's missing to delimit the Select and Insert with Begin and End, but I believe it would be better to use the Merge command:

    merge tbl_Log_ReviewofSystems as t
    using 
    (
        select @QuestionID, @VisitID, @MRNumber, @EnteredBy, @QuestionResponse, EnglishDescription, Category, CategoryCode 
        FROM tbl_Log_ReviewofSystemsValues
        WHERE ID = @QuestionID
    
    ) as s (QuestionID, VisitID, MRNumber, EnteredBy, QuestionResponse, EnglishDescription, Category, CategoryCode)
    on s.QuestionID = t.QuestionID AND s.VisitID = t.VisitID
    when matched then
        UPDATE SET EnteredBy = s.EnteredBy, QuestionResponse = s.QuestionResponse
    when not matched then
        Insert (VisitID, MRNumber, EnteredBy, QuestionID, CategoryCode, Category, EnglishDescription, QuestionResponse) 
        Values (s.VisitID, s.MRNumber, s.EnteredBy, s.QuestionID, s.CategoryCode, s.Category, s.EnglishDescription, s.QuestionResponse)

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 4, 2017 6:24 PM
  • User1122355199 posted

    Thanks so much.  The Begin/End tag fixed the problem.  Also, the MERGE statement worked, but I'm not really sure what the benefits are.  Also, the statement has wlays vexed me syntactically, so I really haven't embraced like I should.

    Thanks again.

    Wednesday, October 4, 2017 7:07 PM
  • User77042963 posted

    In your code, if tbl_Log_ReviewofSystemsValues has unique ID, it should work. But if you have multiple identical ID in that table, you will have issues with your code with assign values to variables.

    Thursday, October 5, 2017 1:36 PM