CODEWITHSUNDEEP
sqlsql-serversql-update
Koketso M
Koketso M

Reputation: 25

Updating with a select one table

I want to update a column with a concatenation of other columns within the same table.

This is for customer names in a table. There are separate columns for "Title" i.e. Mr, Ms, Mrs etc, "FirstName", "MiddleName" and "LastName". I altered the table by adding a new "FullName" column, which I tried to fill with a concatenation of the former columns. 

SET [SalesLT].[Customer].[FullName] = (SELECT 
    Title, 
    FirstName, 
    MiddleName, 
    LastName,
    CONCAT(Title,' ',FirstName,' ',MiddleName,' ',LastName) as FullName
FROM [AdventureWorksLT2008R2].[SalesLT].[Customer])
WHERE FullName = NULL;

I'm getting this

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Upvotes: 0

Views: 53

Answers (1)

fiveobjects
fiveobjects

Reputation: 4289

Since you are updating a column value based on other columns in the same table, you not select them again. 

UPDATE [SalesLT].[Customer]
SET [SalesLT].[Customer].[FullName] = 
    CONCAT(Title,' ',FirstName,' ',MiddleName,' ',LastName)
WHERE FullName is NULL;

Note: I did not change your where clause condition, neither concat function. In some databases you may need to do FullName is NULL instead of FullName = NULL. And in some databases, you may need to concat multiple values with ||.

Upvotes: 1

Related Questions