Re: Insert data from CONCAT data from one table using select statement into new table

[Tim burke]

This is what I get from the second code.

MariaDB [test]> select u.username, u.idnumber, p.idnumber from person p left join username u using (idnumber);
+----------+----------+----------+
| username | idnumber | idnumber |
+----------+----------+----------+
| NULL | NULL | 1000 |
| NULL | NULL | 1001 |
| NULL | NULL | 1002 |
| NULL | NULL | 1003 |
| NULL | NULL | 1004 |
| NULL | NULL | 1005 |
| NULL | NULL | 1006 |
| NULL | NULL | 1007 |
| NULL | NULL | 1008 |
| NULL | NULL | 1009 |
| NULL | NULL | 1010 |
| NULL | NULL | 1011 |
| NULL | NULL | 1012 |
| NULL | NULL | 1013 |
| NULL | NULL | 1014 |
| NULL | NULL | 1015 |
| NULL | NULL | 1016 |
+----------+----------+----------+
17 rows in set (0.017 sec)




Here is the person table.

MariaDB [test]> SELECT * FROM person;
+----------+-----------+-----------+-------------+
| IDNumber | LastName | FirstName | MemberSince |
+----------+-----------+-----------+-------------+
| 1000 | Smith | JoAnne | 2014-01-03 |
| 1001 | Roberts | Clive | 2014-02-19 |
| 1002 | Hsiao | Amy | 2014-03-03 |
| 1003 | Juarez | Ronald | 2015-04-01 |
| 1004 | Jones | Laurie | 2015-04-16 |
| 1005 | Ackerblad | Sam | 2015-04-27 |
| 1006 | Schmidt | Tom | 2015-05-01 |
| 1007 | Cocker | Tim | 2016-01-05 |
| 1008 | Jones | Frank | 2016-02-17 |
| 1009 | Alder | Jason | 2016-03-12 |
| 1010 | Dario | John | 2016-04-22 |
| 1011 | Wise | Skyler | 2016-05-08 |
| 1012 | Cardwell | Phil | 2016-07-25 |
| 1013 | Cornell | Dan | 2016-08-07 |
| 1014 | Foster | Jack | 2016-10-01 |
| 1015 | Brown | Bob | 2016-11-15 |
| 1016 | Miller | Adam | 2016-12-06 |
+----------+-----------+-----------+-------------+
17 rows in set (0.000 sec)





This is the username table.

MariaDB [test]> SELECT * FROM username;
Empty set (0.000 sec)






And the description of the username table.

MariaDB [test]> DESCRIBE username;
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| usernameID | int(11) | NO | PRI | NULL | auto_increment |
| IDNumber | int(11) | NO | MUL | NULL | |
| username | char(6) | NO | | NULL | |
+------------+---------+------+-----+---------+----------------+
3 rows in set (0.030 sec)




And this data is what I want to populate the username table in the username column. with the primary key and foreign key to populate as well.

MariaDB [test]> SELECT (concat(substring(LastName,1,2),substring(IDNumber,1,4))) username FROM Person;
+----------+
| username |
+----------+
| Sm1000 |
| Ro1001 |
| Hs1002 |
| Ju1003 |
| Jo1004 |
| Ac1005 |
| Sc1006 |
| Co1007 |
| Jo1008 |
| Al1009 |
| Da1010 |
| Wi1011 |
| Ca1012 |
| Co1013 |
| Fo1014 |
| Br1015 |
| Mi1016 |
+----------+
17 rows in set (0.000 sec)




I'm sorry I'm learning now. Thank you very much for your help so far.