SQL Query - Insert values from two columns in another table into one column

Question

Can anyone please help me with an SQL query which will allow me to populate the real_name column in table 1 with values from both first_name and second_name columns in table 2 by referencing the usernames.

TABLE 1

audit_username	real_name
AHolland
THardy
LGentle
BGreen

TABLE 2

second_name	first_name	username
Holland	Andrew	AHolland
Hardy	Tom	THardy
Gentle	Lauren	LGentle
Green	Barry	BGreen

Answer 1

It's unclear to me whether you just want to execute a query or to really change the data in table1. If you want the query only:

SELECT
  t1.audit_username,
  CONCAT(t2.first_name, ' ', t2.second_name) AS real_name
FROM
  table1 t1 INNER JOIN table2 t2
    ON t1.audit_username = t2.username;

If you want to change the data:

UPDATE 
  table1 t1 INNER JOIN table2 t2 
    ON (t1.audit_username = t2.username)
SET 
  t1.real_name = CONCAT(t2.first_name, ' ', t2.second_name);

See here this will work correctly.

Note: Take care to use the correct syntax to update the table! The syntax provided in this answer is invalid for a MySQL DB.

I recommend to read some documentations, for example this one about the correct syntax for such update commands.