Updateing a column from select statment

Debilon 431 Reputation points
2022-02-26T20:18:06.07+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
{count} votes

Accepted answer
  1. LiHong-MSFT 10,046 Reputation points
    2022-02-28T06:13:44.18+00:00

    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  
    
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Viorel 112.9K Reputation points
    2022-02-26T20:43:09.883+00:00

    Try something like this:

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

  2. Erland Sommarskog 102.3K Reputation points
    2022-02-26T23:03:29.39+00:00

    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
    
    0 comments No comments