SQL statement - update on two tables

Question

Hello - I am a newbie to SQL. please help with this. I have the below SQL which updates a table for company ticker = SUN from another table that has many records for ticker 'SUN' and I need to use only one record matching 'Float'

the below works if I state 'SUN', however, I want to do this for 4000+ ticker symbols. How do I do it?

UPDATE dbo.Company
SET dbo.Company.[Float] = 
    ( SELECT dbo.yahoo.[Value Mast]
          FROM dbo.yahoo
          WHERE dbo.yahoo.[Value Category]= 'Float' and dbo.yahoo.symbol = 'SUN') 
Where dbo.Company.tick = 'SUN'

table company looks like -

Tick
Float

table yahoo looks like Symbol Value categoty (one of the value category here is FLOAT) Value mast (I am updating this value in company table for value category =float

thanks

Answer 1

This will update the value of Float for all the rows in Company:

UPDATE 
  dbo.Company
SET 
  dbo.Company.[Float] = (
    SELECT
      dbo.yahoo.[Value Mast] 
    FROM
      dbo.yahoo 
    WHERE 
      dbo.yahoo.[Value Category] = 'Float'
      AND dbo.yahoo.symbol = dbo.Company.tick
  )

The sub-query now references the outer query to get the symbol name to look up.

Answer 2

This my friend is a job for the update join.

update company set [float] = y.[value mast] 
from 
    company c 
    inner join yahoo y 
         on y.symbol = c.tick 
         and y.[value category] = 'float'

Answer 3

Try this:

UPDATE Company
SET [Float] = yahoo.[Value Mast]
FROM
  yahoo
  INNER JOIN Company ON yahoo.symbol = Company.tick
WHERE
  yahoo.[Value Category]= 'Float'

Joins can be used in the FROM statement of an UPDATE, as long as you only update one table.