1

I will try to explain this as best as possible and I do apologize in advance if this does not make sense. I have two columns (First_Name and Last_Name) from one table (Table.a) where a lot of the data are incorrect. I am trying to update those columns with the correct data from another table (Table.b). The issue is that the column for table.b contain both the first name and last name data in one column. For example, table.a column and value is First_Name = Richard and Last_Name = Johnsondev while table.b column and value is Full_Name = Johnsondev, Richard. Is there a way to update table.a two columns using portions of table.b data? The only consistent is that table.b last name ends with 'dev,' like "Johnsondev, Richard"

EDIT:

I am using Microsoft SQL Server. Hopefully the below information is helpful!

Current Table Data

TABLE.B

FULL_NAME
Johnsondev, Richard
Smithdev, Kevin

TABLE.A

FIRST_NAME / LAST_NAME
Richard    / Jacksondev 
Kevin      / Standev

Expected Output using a query

TABLE.B stays the same

TABLE.A 
FIRST_NAME / LAST_NAME
Richard    / Johnsondev 
Kevin      / Smithdev

Picture of scenario

Emma
  • 27,428
  • 11
  • 44
  • 69
James
  • 13
  • 4
  • can you please provide some sample data in [here](https://dbfiddle.uk/) or add to the question with expected output? – sacse Aug 07 '20 at 14:56
  • 2
    Hi James. A couple of things: You've tagged both mysql and also sql-server (which is Microsoft). The syntax, and answer may be different depending on what version of sql you are actually using. Can you also provide more of an example of table b please? Paste in a number of rows of data and also advise what you have tried already. You query does make sense. You are basically trying to split that column up, but we need to see what the data looks like and also for you to advise what version of SQL you are using to provide an accurate answer. Cheers – Yarner Aug 07 '20 at 15:00
  • Ahh sorry im still new to sql as your can tell lol.. I just made a edit to my post – James Aug 07 '20 at 15:15
  • On PostgreSQL you can use [this](https://stackoverflow.com/questions/13473499/update-a-column-of-a-table-with-a-column-of-another-table-in-postgresql#13473660). – Flaviu Apr 14 '21 at 19:29
  • On PostgreSQL, you can use [this](https://stackoverflow.com/questions/13473499/update-a-column-of-a-table-with-a-column-of-another-table-in-postgresql). – Flaviu Apr 14 '21 at 19:30

2 Answers2

1

If you use the JOIN method in tlk27's answer, this would be the syntax for SQL Server:

UPDATE a SET FIRST_NAME=SUBSTRING(b.FULL_NAME, CHARINDEX(',',b.FULL_NAME) +1, Len(b.FULL_NAME)), 
LAST_NAME=LEFT(b.FULL_NAME, CHARINDEX(',',b.FULL_NAME)-1)
FROM Table_A a JOIN Table_B b ON a.ID = b.ID -- assumes a common ID field
CB_Ron
  • 589
  • 4
  • 13
  • Thank you very much this was very helpful! I just learned new things from this query! – James Aug 07 '20 at 19:56
  • @James you're welcome! Please mark it as the answer if it suits your needs. – CB_Ron Aug 07 '20 at 20:57
  • Is there a possible way to use that same query that you have provided and use it to find how many has the issue? Im trying to use the query you provided as a SELECT statement but I am running into errors. – James Aug 10 '20 at 20:08
  • What do you mean? Do you want the `SELECT` to return the rows where the names are not the same in both tables? – CB_Ron Aug 10 '20 at 20:15
  • `SELECT COUNT(*) AS NotEqual FROM Table_A a JOIN Table_B b ON a.ID=b.ID WHERE b.FULL_NAME<>a.LAST_NAME + ',' + a.FIRST_NAME;` – CB_Ron Aug 10 '20 at 20:40
  • Thank you! Sorry i also had one final question. Is there a way to find a portion of the full name? If the full name had Last Name, First Name and MIddle initial (Ex. Johnson, David R.) and I just want to pull out the name David. Is there a way to do that? I used the query LTRIM(SUBSTRING(Column, CHARINDEX(',',Column) +1, Len(Column))) and it removes the last name but not sure how to remove the last name and the middle initial. – James Aug 11 '20 at 17:01
  • It's not going to be pretty, and may not take into account all possible issues. In your original post, the sample data had no space between `last_name,first_name`. If that is the case, then this should work. `SELECT LEFT(SUBSTRING(Column, CHARINDEX(',',Column) +1, LEN(Column)), CHARINDEX(' ', SUBSTRING(Column, CHARINDEX(',',Column) +1, LEN(Column))) -1)` BTW you never marked my answer as the correct one. Please do so! Thanks! – CB_Ron Aug 11 '20 at 17:22
  • Oh sorry ! first time on stack overflow and didnt see the correct button! will do that! – James Aug 11 '20 at 17:37
0

In MySQL you could do something like below. Depending on your setup this might need modification. However, for LAST_NAME we are selecting everything up to the specified delimiter ','. For FIRST_NAME we are selecting everything to the right of what we used to get last name. Note, this will update all the names. You could use WHERE after `

UPDATE Table_A a
SET a.FIRST_NAME = (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(FULL_NAME, ',', 2), ' ', -1)
                    FROM TABLE_B),
    a.LAST_NAME = (SELECT SUBSTRING_INDEX('Johnsondev, Richard', ',', 1)
                   FROM TABLE_B),

Alternatively, you might need to join them together.

UPDATE Table_A a
JOIN Table_B b ON b.ID = a.ID -- assumes a common ID field
SET a.FIRST_NAME = (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(FULL_NAME, ',', 2), ' ', -1)
                    FROM TABLE_B),
    a.LAST_NAME = (SELECT SUBSTRING_INDEX('Johnsondev, Richard', ',', 1)
                   FROM TABLE_B),

EDIT Per your request, after some testing it looks like these could be alternatives in SQL Server.

SUBSTRING: SUBSTRING ( expression ,start , length )
CHARINDEX: CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

SELECT SUBSTRING('Johnsondev, Richard', CHARINDEX(',','Johnsondev, Richard') +1, Len('Johnsondev, Richard')) -- Richard

SELECT LEFT('Johnsondev, Richard',CHARINDEX(',','Johnsondev, Richard')-1) -- Johnsondev

With your variables:

SELECT SUBSTRING(FULL_NAME, CHARINDEX(',',FULL_NAME) +1, Len(FULL_NAME))
FROM TABLE_B

SELECT LEFT(FULL_NAME, CHARINDEX(',',FULL_NAME)-1)
FROM TABLE_B

I am not too familiar with SQL Server syntax for UPDATE but you can find it here.

tlk27
  • 309
  • 2
  • 10