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?

You don't have the DDL for your tables, but assuming that str_key and other_key make up the PK for your ValTable, is there a reason that you have the "LIMIT 1"? Even if it's not the PK, it seems odd to be using LIMIT here — Tom H
– Tom H, Commented Apr 8, 2009 at 11:15
I wasn't sure whether it would cause a sweep of the entire table without the limit in there, but I suppose it's smart enough to know that the compound key is the key and only affect that one row. — Phil H
– Phil H, Commented Apr 8, 2009 at 13:04

David M · Accepted Answer · 2009-04-08 13:27:10Z

14

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

edited Apr 8, 2009 at 13:27

answered Apr 8, 2009 at 11:09

David M

73.2k13 gold badges164 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Phil H Over a year ago

How do I modify the second line? Can I just do this: IF @@ROWCOUNT=0 INSERT INTO v VALUES (v.str_key, 1, 2.124) Or do I need to re-determine v.str_key using another join?

DBAndrew · Accepted Answer · 2009-04-08 18:31:59Z

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

Collectives™ on Stack Overflow

SQL update with a join?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related