MySQL - Set field values as values from another table

0
votes

I was wondering which would be the right query statement to take values from a table and insert them into another in a way similar to using the WHERE clause

Example -

Table1 contains fields: registrynumber - name

Table2 contains fields: id - firstname - lastname

The query must set Table1.name = Table2.firstname where Table1.registrynumber and Table2.id have the same values at the rows of Table1.name and Table2.firstname.

I hope i made it clear enough. Regards.

2
mysqlsqljoinwhere
Are you updating or inserting? That is not clear from your answer – Brian Webster
The best way I can think of is to drop the name field from table1. Part of database normalization is to store each piece of data only once. – Dan Bracuk

2 Answers

1
votes
UPDATE Table2 tb2
SET tb2.firstname = ( SELECT name FROM Table1 WHERE registryname = tb2.id )
0
votes
INSERT INTO Table1 (registrynumber, name)
SELECT id, firstname FROM Table2;