SQL UPDATE - How to update a column from one table using another table?

Question

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

can you please provide some sample data in here or add to the question with expected output? — sacse
– sacse, Commented Aug 7, 2020 at 14:56
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
– Yarner, Commented Aug 7, 2020 at 15:00
Ahh sorry im still new to sql as your can tell lol.. I just made a edit to my post — James
– James, Commented Aug 7, 2020 at 15:15

CB_Ron · Accepted Answer · 2020-08-07 18:31:41Z

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

answered Aug 7, 2020 at 18:31

CB_Ron

6097 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

James Over a year ago

Thank you very much this was very helpful! I just learned new things from this query!

CB_Ron Over a year ago

@James you're welcome! Please mark it as the answer if it suits your needs.

James Over a year ago

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.

CB_Ron Over a year ago

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 Over a year ago

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;

|

tlk27 · Accepted Answer · 2020-08-07 17:42:54Z

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.

Collectives™ on Stack Overflow

SQL UPDATE - How to update a column from one table using another table?

EDIT:

Current Table Data

2 Answers 2

8 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

EDIT:

Current Table Data

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related