Updateing a column from select statment

Question

Hi guys i am a newbie so have some patient

I have an owner table with a field containing the owner name

I would like to separate the owner name to first name, last name, middle initial or Corp Name

I wrote a query to find all the names that represent corporations

Select NAME1, name1 as NameCorp
into TempTbl
FROM [Account].[dbo].[OWNER]
where CHARINDEX(',' ,name1) = 0 and NAME1 Like '%LLC%' or CHARINDEX(',' ,name1) = 0 and NAME1 Like '%INC%'

and i am getting a list of all the Corporations.
That part works fine.

I added a column in the same Owner Table named NameCorp

How do i update the column and delete the value from the old column Name 1

Thank You

Answer 1

Hi @Debilon

Updateing a column from select statement
How do i update the column and delete the value from the old column Name 1

Please check this:

With CTE AS   
(  
 SELECT NAME1,NameCorp  
 FROM [Account].[dbo].[OWNER]  
 WHERE CHARINDEX(',' ,name1) = 0 and NAME1 Like '%LLC%' or CHARINDEX(',' ,name1) = 0 and NAME1 Like '%INC%'  
)  
UPDATE CTE   
SET NameCorp=NAME1,NAME1=NULL  

Answer 2

Try something like this:

update t
set NameCorp = NAME1, 
    NAME1 = null
from [Account].dbo.[OWNER] t
where . . .

Answer 3

You have a primary key for your table, I hope? Else it will be difficult! But assuming that you have a PK named Id, you can do:

UPDATE Owner
SET    NameCorp = t.Name,
       Name1 = NULL
FROM  Owner o
JOIN TempTbl t ON o.Id = t.id