CODEWITHSUNDEEP
t-sqlsql-server-2012
jlai
jlai

Reputation: 345

MSSQL: Append String from Select

I'd like to add a text to nvarchar field based on a select from another table.

When I try this I get the following error: The multi-part identifier "employees.desc" could not be bound

UPDATE
    employees
SET
    employees.desc = employees.desc + ', ' + c.title
FROM
    employees AS e
    INNER JOIN company AS c
        ON e.dept=c.orgcode
WHERE e.dept IS NOT NULL

If I just set a new title without appending to the existing one using 

employees.desc = c.title

it works ok. What am I doing wrong here?

Upvotes: 2

Views: 72

Answers (3)

dybzon
dybzon

Reputation: 1594

You could re-write it like this:

UPDATE
    e
SET
    desc = e.desc + ', ' + c.title
FROM
    employees AS e
    INNER JOIN company AS c
        ON e.dept=c.orgcode
WHERE e.dept IS NOT NULL

There is no need to explicitly specify which table you are referring to in your SET clause. I.e. you can write SET desc = ... instead of SET e.desc = .... It's already specified in your UPDATE clause which table you are updating.

Upvotes: 0

Tanveer Badar
Tanveer Badar

Reputation: 5530

Shouldn't your query be this instead?

UPDATE
    employees
SET
    employees.desc = e.desc + ', ' + c.title
FROM
    employees AS e
    INNER JOIN company AS c
        ON e.dept=c.orgcode
WHERE e.deptIS NOT NULL

There's no table named o in your original query.

Upvotes: 1

Peter Smith
Peter Smith

Reputation: 5552

You need to use the aliases from the FROM/JOIN

UPDATE
    employees
SET
    employees.desc = e.desc + ', ' + c.title
FROM
    employees AS e
    INNER JOIN company AS c
    ON e.dept=c.orgcode
WHERE e.deptIS NOT NULL

Upvotes: 2

Related Questions