update one table using data from another table

Question

I am trying to update my current table by drawing data from another table. My database (dbo_finance) column - test

The other database is assestsc and I am going to pull the data from column issuename1, however I only want to pull the issuename1 when the field [MinSecClass] is = 9. This is what I wrote

UPDATE dbo_finance 
SET [dbo_finance].cusip9 = AssetsC.cusip
FROM dbo_finance INNER JOIN AssetsC ON dbo_finance.test = AssetsC.[IssueName1]
WHERE (AssetsC.MinSecClass = 9)

Thanks, first time really using SQL

Answer 1

Well I would use aliases, it's a good habit to get into:

UPDATE f
SET [dbo_finance].cusip9 = AssetsC.cusip 
FROM dbo_finance f 
INNER JOIN AssetsC a ON f.test = a.[IssueName1] 
WHERE (a.MinSecClass = 9) 

Now that will work fine if the assets table will only return one value for cuspid for each record. If this is a one to many relationship you may need to get more complex to truly get the answer you want.

I see several serious design flaws in your table structure. First joins fields that are dependant as something as inherently unstable as issue name are a very poor choice. You want PK and FK field to be unchanging. Use surrogate keys instead and a unique index.

The fact that you have a field called cusp9 indicates to me that you are denormalizing the data. Do you really need to do this? Do you undestand that this update will have to run in a trigger ever time the cuspid assoicated with MinSecClass changes? Whya re you denormalizing? Do you currently have performance problems? A denormalized table like this can be much more difficult to query when you need data from several of these numbered fields. Since you already have the data in the assets table what are you gaining except a maintenance nightmare by duplicating it?

Answer 2

UPDATE dbo_finance
   SET cusip9 = (
                 SELECT A1.cusip
                   FROM AssetsC AS A1 
                  WHERE dbo_finance.test = A1.IssueName1
                        AND AssetsC.MinSecClass = 9
                )
 WHERE EXISTS (
               SELECT *
                 FROM AssetsC AS A1 
                WHERE dbo_finance.test = A1.IssueName1
                      AND A1.MinSecClass = 9
              );

Answer 3

Because you're using SQL 2008, you can take advantage of the new(ish) MERGE statement.

MERGE INTO dbo_finance
USING (SELECT IssueName1, cusip FROM AssetsC WHERE MinSecClass = 9) AS source 
ON dbo_finance.test = source.IssueName1
WHEN MATCHED THEN UPDATE SET dbo_finance.cusip9 = source.cusip;