Updating a table via command that used SELECT

Question

The following SQL Server 2008 query parses a name column into separate first, middle, and last name parts, and works just fine:

SELECT 
  LEFT(Name, CHARINDEX(' ', Name)) AS FirstName, 
  CASE WHEN CHARINDEX(' ', Name) <> LEN(Name) - CHARINDEX(' ', REVERSE(Name)) + 1 THEN 
        SUBSTRING(Name, CHARINDEX(' ', Name)+ 1, LEN(Name) - CHARINDEX(' ', REVERSE(Name))-CHARINDEX(' ', Name)) end as MiddleName, 
  RIGHT( Name, CHARINDEX(' ', REVERSE(Name))) AS LastName 
FROM a01_parse_test

I now need to store the separated parts (FirstName, MiddleName, and LastName) into my table (a01_parse_test). How can I do so?

What edits are you trying to make? Are you trying to change the individual pieces of the `Name` field? What `UPDATE` queries have you tried? — cxw, Jul 10 '15 at 16:37
When you run the command above with SELECT, it displays the proper parsed out names but that's it. I'd like that query modified to be able to actually update the table with the split up names. — Sal, Jul 10 '15 at 16:46
Just to check - you don't have the option of changing the schema to list the name parts in separate columns? :) What SQL server? E.g., on Microsoft, see https://msdn.microsoft.com/en-us/library/ms177561.aspx for examples of string concatenation — cxw, Jul 10 '15 at 16:51
I'm using SQL 2008 and honestly I don't know what you mean . Unfortunately I'm new to SQL and am chopping my way through it. I just figured I could remove select and replace it with update table name and it would just do it like other commands but it didn't so I am totally lost — Sal, Jul 10 '15 at 17:04
@Sal - Please check out the answer below. It allows you to perform updates of just FirstName or the specific parts by using a trigger and view over your source table that just contains a Name. — Jason W, Jul 10 '15 at 18:50

score 1 · Answer 1 · answered Jul 10 '15 at 18:48

Seems like you are wanting to update the original source table that has only Name with distinct changes to FirstName, MiddleName, or LastName. If so, I would consider creating a view on your source table to standardize your logic for splitting the names, then adding a TRIGGER using INSTEAD OF UPDATE on the view to process your updates.

Full example below:

IF OBJECT_ID('a01_parse_test') IS NULL BEGIN
    CREATE TABLE a01_parse_test (Id INT PRIMARY KEY NOT NULL IDENTITY(1,1), Name VARCHAR(500))
    INSERT a01_parse_test VALUES
        ('Barack Hussein Obama'),
        ('George Walker Bush'),
        ('William Jefferson Clinton'),
        ('Ronald Wilson Reagan')
END

IF OBJECT_ID('v_ParseNames') IS NOT NULL
    DROP VIEW v_ParseNames
GO

CREATE VIEW v_ParseNames
AS
    SELECT
        Id, -- Whatever the id is of your parsing table
        LEFT(Name, CHARINDEX(' ', Name)) AS FirstName,
        CASE WHEN CHARINDEX(' ', Name) <> LEN(Name) - CHARINDEX(' ', REVERSE(Name)) + 1
            THEN  SUBSTRING(Name, CHARINDEX(' ', Name)+ 1, LEN(Name) - CHARINDEX(' ', REVERSE(Name))-CHARINDEX(' ', Name)) 
            END AS MiddleName, 
        RIGHT(Name, CHARINDEX(' ', REVERSE(Name))) AS LastName
    FROM a01_parse_test
GO

-- Create instead of update trigger on the view to update names separately
CREATE TRIGGER tg_v_ParseNames_Update ON v_ParseNames
INSTEAD OF UPDATE AS BEGIN
    UPDATE TGT SET
        Name = SRC.FirstName
            + (CASE WHEN LEN(SRC.MiddleName) > 0 THEN ' ' + SRC.MiddleName ELSE '' END)
            + ' ' + SRC.LastName
    FROM a01_parse_test TGT
        INNER JOIN INSERTED SRC
            ON SRC.Id = TGT.Id
END
GO

SELECT * FROM v_ParseNames WHERE Id = 3
UPDATE v_ParseNames SET FirstName = 'Bill' WHERE Id = 3
SELECT * FROM v_ParseNames WHERE Id = 3

So before a change

Id          FirstName       MiddleName      LastName
----------- --------------- --------------- ---------------
1           Barack          Hussein          Obama
2           George          Walker           Bush
3           William         Jefferson        Clinton
4           Ronald          Wilson           Reagan

The UPDATE statement then can just update a first name:

UPDATE v_ParseNames SET FirstName = 'Bill' WHERE Id = 3

Then after the update statement ran:

Id          FirstName       MiddleName      LastName
----------- --------------- --------------- ---------------
3           Bill            Jefferson        Clinton

score 0 · Accepted Answer · edited May 23 '17 at 10:26

0

Edited You can use the functions directly in an UPDATE statement (per this). Assuming you have columns Name, FirstName, MiddleName, and LastName in your table:

UPDATE a01_parse_test
SET FirstName=LEFT(Name, CHARINDEX(' ', Name)), 
    MiddleName=CASE WHEN CHARINDEX(' ', Name) <> LEN(Name) - CHARINDEX(' ', REVERSE(Name)) + 1 THEN 
        SUBSTRING(Name, CHARINDEX(' ', Name)+ 1, LEN(Name) - CHARINDEX(' ', REVERSE(Name))-CHARINDEX(' ', Name)) END, 
    LastName=RIGHT( Name, CHARINDEX(' ', REVERSE(Name)))

If those columns don't exist in your table, you'll need to add them using ALTER TABLE:

ALTER TABLE a01_parse_test
ADD FirstName VARCHAR(50) NULL, 
    MiddleName VARCHAR(50) NULL, 
    LastName VARCHAR(50) NULL

edited May 23 '17 at 10:26

Community

1
1

answered Jul 10 '15 at 17:22

cxw

16,685
2
45
81

Don't think those will work. If the name parts were in one field and I wanted to join them to make a full name then yes.. or to replace a value with another value then yes but what I have is a column called NAME. There are other columns called firstname, middlename and lastname that are empty. Running the above TSQL I provided it effectively parses the full name to its parts... but only in the viewing pane due to the SELECT statement. What I need is the table updated (the firstname to firstname field, etc) Basically I need my command to actually change the physical table not just in view pane. – Sal Jul 10 '15 at 17:29
So you're actually trying to parse a combined Name field of a record and stash the results in separate fields in the same record of the same table? – cxw Jul 10 '15 at 17:41
Exactly :-) That TSQL I provided does just that, but only in viewing pane not actually changing the data in any way. That part eludes me. – Sal Jul 10 '15 at 17:43
if the query was this, how would it be restructured? select LEFT(Name, CHARINDEX(', ', Name) - 1) LastName, SUBSTRING(Name, CHARINDEX(', ', Name) + 2, CASE WHEN CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) = 0 THEN LEN(Name) + 1 ELSE CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) END - CHARINDEX(', ', Name) - 2) FirstName, RIGHT(Name, LEN(Name) - CASE WHEN CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) = 0 THEN LEN(Name) ELSE CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) END) Mid from a01_parse_test – Sal Jul 10 '15 at 21:23
ta daa! UPDATE a01_parse_test SET lastName=LEFT(Name, CHARINDEX(', ', Name) - 1), firstname=SUBSTRING(Name, CHARINDEX(', ', Name) + 2, CASE WHEN CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) = 0 THEN LEN(Name) + 1 ELSE CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) END - CHARINDEX(', ', Name) - 2), middlename=RIGHT(Name, LEN(Name) - CASE WHEN CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) = 0 THEN LEN(Name) ELSE CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) END) – Sal Jul 10 '15 at 21:48

Updating a table via command that used SELECT

2 Answers2