Geek Answers Handbook

SQL update with join?

I have two tables. One of them is a simple string / identifier:

StrTable:

  str_key String
 0 'a'
 1 'b'

where the lines are unique. The other is more complex and includes a common string_id string

ValTable:

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

Now I want to update ValTable using the string I'm looking for to get str_key via StrTable. Simple update:

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 change this to enable str_key search with some string 'a'? I suppose I need to join, but I never did join the update. Or can I just add more to my where clause?

+4
source share
2 answers

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
+14
source

The above example by David M is valid and works. Depending on the size of the table, you can avoid "Blind updates", as this can lead to performance problems in VERY large tables. Pay attention to table hints in 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
+1
source

More articles:


All Articles