CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2012
Mark Santos
Mark Santos

Reputation: 108

How to update column with data from another table? - SQL Server 12.0.5

I am trying to update the column "efficiency" in the table "SUS_WK" with the data from the column "custom_number_8" from the table "SC_PROD". But I only want it to update if certain requirements are met, such as the "ID" from table "SUS_WK" matches the "ID" from the table "SC_PROD".

How can I do this?

I have tried to do this:

    UPDATE SUS_WK
    SET efficiency = SC_PROD.custom_number_8
    FROM SUS_WK t
        JOIN SC_PROD p
        ON t.id = p.id

When I tried the code above, I get the following error:

The multi-part identifier "SC_PROD_PLAN_PLND.custom_number_8" could not be bound.

But I expect the result of that code to update the column "efficiency" in the "SUS_WK" with the data from column "custom_number_8" in the table "SC_PROD".

Upvotes: 1

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You are on the right track. Just use the table alias rather than the table name:

UPDATE t
    SET efficiency = p.custom_number_8
    FROM SUS_WK t JOIN
          SC_PROD p
          ON t.id = p.id;

I strongly recommend using the table alias for the UPDATE as well. SQL Server will resolve the table name to be the same as the t -- but depending on that makes the query rather hard to decipher (because references to the same table have difference aliases).

Upvotes: 1

Related Questions