CODEWITHSUNDEEP
sqlt-sqlprimary-key
James Merrill
James Merrill

Reputation: 23

How to get Primary Key on SQL Update?

I'm updating a table if "searchedWord" is found. If it is found I would like to know what the primary key is for the record I just updated. How would I do this? Below is my code. On a side note, if "searchedWord" is not found I insert it and use scope_identity() to get it. My problem is how to do the same with Update? I guess I could do a select statement first but that is an additional query.

BEGIN TRAN    
    UPDATE COBC_searchedWord 
    SET searchCount=searchCount+1, 
        resultsReturned=#resultsReturned#, 
        lastSearched=getDate() 
    WHERE searchedWord='#searchedWord#';

    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO COBC_searchedWord
            ([searchedWord] ,[searchCount] ,[resultsReturned] ,[lastSearched])
        VALUES
            ('#searchedWord#',1,'#resultsReturned#',getDate())
        SELECT scope_identity() AS searchedWordID
    END
COMMIT TRAN

Update: This is what I ended up with and it seems to work. Thanks for the input.

DECLARE @tmpTable TABLE (ID int);  
UPDATE COBC_searchedWord
SET searchCount=searchCount+1, 
    resultsReturned=#resultsReturned#, 
    lastSearched=getDate()  
OUTPUT inserted.searchedWordID into @tmpTable          
WHERE searchedWord='#searchedWord#';          

IF @@ROWCOUNT = 0  
BEGIN  
    INSERT INTO COBC_searchedWord  
        ([searchedWord])         
        OUTPUT inserted.searchedWordID into @tmpTable  
    VALUES  
        ('#searchedWord#');  
END  
SELECT ID from @tmpTable;

Upvotes: 2

Views: 1402

Answers (2)

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

Try this, if you are updating your record, and that searchword focund in table then select the searchedWordID from table

BEGIN TRAN    
    UPDATE COBC_searchedWord 
    SET searchCount=searchCount+1, 
        resultsReturned=#resultsReturned#, 
        lastSearched=getDate() 
    WHERE searchedWord='#searchedWord#';

    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO COBC_searchedWord
            ([searchedWord] ,[searchCount] ,[resultsReturned] ,[lastSearched])
        VALUES
            ('#searchedWord#',1,'#resultsReturned#',getDate())
        SELECT scope_identity() AS searchedWordID
    END
    ELSE
    BEGIN
      SELECT searchedWordID FROM COBC_searchedWord 
      WHERE searchedWord='#searchedWord#';
    END
COMMIT TRAN

Upvotes: 0

HLGEM
HLGEM

Reputation: 96552

You should use the OUTPUT cluase. Books online will explain how.

Upvotes: 5

Related Questions