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

[Tim burke]

So I got what I wanted done but I think there must be a much easier way to do it. If my data set was large this would not be realistic. I had to do the insert into statement for each record. Is there a way to do this insert into statement for all users automatically instead of individually?

Anyway here is what I did.



MariaDB [test]> CREATE TABLE username (
-> usernameID INT AUTO_INCREMENT PRIMARY KEY,
-> IDNumber INT NOT NULL,
-> username CHAR(6),
-> FOREIGN KEY (IDNumber) REFERENCES person(IDNumber));
Query OK, 0 rows affected (0.062 sec)

MariaDB [test]> INSERT INTO username VALUES (usernameID, '1000', '');
Query OK, 1 row affected (0.019 sec)

MariaDB [test]> INSERT INTO username VALUES (usernameID, '1001', '');
Query OK, 1 row affected (0.002 sec)

MariaDB [test]> INSERT INTO username VALUES (usernameID, '1002', '');
Query OK, 1 row affected (0.002 sec)

MariaDB [test]> INSERT INTO username VALUES (usernameID, '1003', '');
Query OK, 1 row affected (0.002 sec)

MariaDB [test]> INSERT INTO username VALUES (usernameID, '1004', '');
Query OK, 1 row affected (0.002 sec)

MariaDB [test]> INSERT INTO username VALUES (usernameID, '1005', '');
Query OK, 1 row affected (0.002 sec)

MariaDB [test]> INSERT INTO username VALUES (usernameID, '1006', '');
Query OK, 1 row affected (0.002 sec)

MariaDB [test]> INSERT INTO username VALUES (usernameID, '1007', '');
Query OK, 1 row affected (0.002 sec)

MariaDB [test]> INSERT INTO username VALUES (usernameID, '1008', '');
Query OK, 1 row affected (0.002 sec)

MariaDB [test]> INSERT INTO username VALUES (usernameID, '1009', '');
Query OK, 1 row affected (0.002 sec)

MariaDB [test]> INSERT INTO username VALUES (usernameID, '1010', '');
Query OK, 1 row affected (0.002 sec)

MariaDB [test]> INSERT INTO username VALUES (usernameID, '1011', '');
Query OK, 1 row affected (0.002 sec)

MariaDB [test]> INSERT INTO username VALUES (usernameID, '1012', '');
Query OK, 1 row affected (0.002 sec)

MariaDB [test]> INSERT INTO username VALUES (usernameID, '1013', '');
Query OK, 1 row affected (0.002 sec)

MariaDB [test]> INSERT INTO username VALUES (usernameID, '1014', '');
Query OK, 1 row affected (0.002 sec)

MariaDB [test]> INSERT INTO username VALUES (usernameID, '1015', '');
Query OK, 1 row affected (0.002 sec)

MariaDB [test]> INSERT INTO username VALUES (usernameID, '1016', '');
Query OK, 1 row affected (0.001 sec)

MariaDB [test]> UPDATE username u
-> JOIN person p USING(IDNumber)
-> SET u.username = concat(substring(FirstName,1,1),substring(LastName,1,1),substring(IDNumber,1,4));
Query OK, 17 rows affected (0.017 sec)
Rows matched: 17 Changed: 17 Warnings: 0

MariaDB [test]> select * from username;
+------------+----------+----------+
| usernameID | IDNumber | username |
+------------+----------+----------+
| 1 | 1000 | JS1000 |
| 2 | 1001 | CR1001 |
| 3 | 1002 | AH1002 |
| 4 | 1003 | RJ1003 |
| 5 | 1004 | LJ1004 |
| 6 | 1005 | SA1005 |
| 7 | 1006 | TS1006 |
| 8 | 1007 | TC1007 |
| 9 | 1008 | FJ1008 |
| 10 | 1009 | JA1009 |
| 11 | 1010 | JD1010 |
| 12 | 1011 | SW1011 |
| 13 | 1012 | PC1012 |
| 14 | 1013 | DC1013 |
| 15 | 1014 | JF1014 |
| 16 | 1015 | BB1015 |
| 17 | 1016 | AM1016 |
+------------+----------+----------+
17 rows in set (0.000 sec)