Solved

update a table based on a query of itself

Posted on 2013-01-28
6
157 Views
Last Modified: 2013-01-29
Good Afternoon Experts,

I have a table that is storing a name column as a full name and I want to split it up as first name and last name.  The data is pretty clean, one space between first and last name in each case, no initals etc so I can do a select to seperate the data for example :

SELECT
  SUBSTRING(Loan_Officer, 1, CHARINDEX(' ', Loan_Officer) - 1) AS LO_First_Name,
  SUBSTRING(Loan_Officer, CHARINDEX(' ', Loan_Officer) + 1, 8000) AS LO_Last_Name
FROM
  Custom_LO_Commission

but how do I then take that data and update Custom_LO_Commission table
to insert the two new columns LO_FirstName and LO_LastName with the correct data per record?
0
Comment
Question by:ISBTECH
6 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 38828878
Actually, you've already done the hard work.  :)


UPDATE mytable
SET (firstname, lastname) =
  (SUBSTRING(Loan_Officer, 1, CHARINDEX(' ', Loan_Officer) - 1),
  SUBSTRING(Loan_Officer, CHARINDEX(' ', Loan_Officer) + 1, 8000))
FROM
  Custom_LO_Commission


Good Luck,
Kent
0
 

Author Comment

by:ISBTECH
ID: 38828897
That gives me
Incorrect syntax near '('.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 38828918
You could do that in a AFTER INSERT/UPDATE trigger however my recommandations are to do it in the code that populates the table instead.

You need to put that trigger on the parent table where the INSERT or UPDATE takes place and the code will be something like:

CREATE TRIGGER [dbo].[Trigger_Custom_LO_Commission]
    ON [dbo].Custom_LO_Commission
    AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON

UPDATE Custom_LO_Commission
SET LO_First_Name = SUBSTRING(Loan_Officer, 1, CHARINDEX(' ', Loan_Officer) - 1),
      LO_Last_Name  = SUBSTRING(Loan_Officer, CHARINDEX(' ', Loan_Officer) + 1, 8000)
FROM
  Custom_LO_Commission
WHERE Id = (SELECT Id FROM INSERTED)

END;


--where ID from "WHERE Id = (SELECT Id FROM INSERTED)" is the ROW identifier for that Loan_Officer fullname.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 39

Expert Comment

by:appari
ID: 38829468
try this

UPDATE Custom_LO_Commission
SET firstname =    SUBSTRING(Loan_Officer, 1, CHARINDEX(' ', Loan_Officer) - 1) ,
 lastname = SUBSTRING(Loan_Officer, CHARINDEX(' ', Loan_Officer) + 1, 8000)
0
 
LVL 10

Accepted Solution

by:
deviprasadg earned 500 total points
ID: 38830508
Add computed firstname and lastname columns to the table.

ALTER TABLE  Custom_LO_Commission ADD firstname AS SUBSTRING(Loan_Officer, 1, CHARINDEX(' ', Loan_Officer) - 1)
ALTER TABLE  Custom_LO_Commission ADD lastname AS SUBSTRING(Loan_Officer, CHARINDEX(' ', Loan_Officer) + 1, 8000)

Open in new window

0
 

Author Closing Comment

by:ISBTECH
ID: 38830850
That did it and added the new columns all in one statement, Nice!

Thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now