SQL update with a join?

Question

I have two tables. One is simple string/ID look up:

StrTable:

str_key String
0       'a'
1       'b'

where the strings are unique. The other is more complex, and includes the shared string_id

ValTable:

str_key other_key val
0        0        1.234
0        1        1.567
1        0        1.890

Now, I want to do an update on ValTable, using a string which I lookup to get the str_key via StrTable. The simple update would be:

UPDATE ValTable SET val = 2.124 WHERE str_key = 0 AND other_key = 1 LIMIT 1
IF @@ROWCOUNT=0 INSERT INTO ValTable VALUES (0,1,2.124);

So how can I modify this to include looking up the str_key with some string 'a'? I assume I need a join, but I've never done a join in an update. Or can I just add more to my where clause?

Answer 1

This is the syntax you need:

UPDATE  v
SET     val = 2.124
FROM    ValTable v
        INNER JOIN
                StringTable s
                ON v.str_key = s.str_key
WHERE   s.String = 'a'
AND     v.other_key = 1

IF @@ROWCOUNT = 0
BEGIN

        INSERT
        INTO    ValTable
        SELECT  str_key, 1, 2.124
        FROM    StringTable
        WHERE   String = 'a'

END

Answer 2

The Above example by David M is valid and works. Depending on the size of your table you may want to avoid "Blind Updates" as this can cause performance issues on VERY large tables. Note the table hints within the IF EXISTS().

IF EXISTS(
        SELECT 
            * 
        FROM 
            ValTable v WITH(NOLOCK)
            INNER JOIN StringTable s WITH(NOLOCK) ON v.str_key = s.str_key 
        WHERE 
            s.String = 'a' 
        AND v.other_key = 1
    )
BEGIN
    UPDATE  
        v
    SET     
        val = 2.124
    FROM    
        ValTable v
        INNER JOIN StringTable s ON v.str_key = s.str_key
    WHERE   
        s.String = 'a'
    AND v.other_key = 1
END
ELSE
BEGIN
    INSERT INTO ValTable
        --(You should define your columns here, You didn't provide a sample schema so I don't know what your columns are.)
        --(Col1,COl2,COl3,etc...)
    SELECT  
        str_key, 1, 2.124
    FROM    
        StringTable
    WHERE   
        String = 'a'
END