How do I insert a column of values from one table to another, non-matching schemas?

Question

I have two tables:
Table A: lastName,firstName,clientExtension
Table B: ~45 columns, however lastName,firstName,clientExtension are also in this table. The data types for these three columns match in each table.. lastName VARCHAR(150),firstName VARCHAR(150),clientExtension INT(5) unsigned.

Table A has 31 rows, no NULL values. The records in Table A are already in Table B, but my objective is to update the clientExtension value in Table B to be the clientExtension value from Table A for each agent.

This is what I have tried so far, with no luck..

INSERT INTO table_A (lastName, firstName, clientExtension)
SELECT clientExtension
FROM tableB AS tb
WHERE lastName=tb.lastName
AND firstName=tb.firstName;

I've also tried using the UPDATE function, however I can't seem to get it to work. It feels like what I'm trying to do is an INNER JOIN, except I'm not looking to create a new table with the output of the INNER JOIN, I'm looking to update existing records in Table B with the clientExtension values in Table A.

Any ideas??

my idea is that if you want to do an update, do an update/join pattern. — Drew, Aug 26 '15 at 17:22

Drew · Accepted Answer · 2015-08-26T18:12:24.727

This schema needs some help before you have more than a few dozen rows in those tables. If that is really your schema, then you have some problems when names change. It will take a few minutes to show a better approach, bear with me.

Then I will show the update/join pattern if you don't have it yet (on the better schema).

create table tableA
(   -- assuming this is really a user table
    id int auto_increment primary key, -- if you don't have this, you are going to have problems
    firstName varchar(150) not null,
    lastName varchar(150) not null,
    clientExtension int not null -- sign, display width of no concern
);-- sign, display width of no concern
insert tableA (firstName,lastName,clientExtension) values ('f1','l1',777),('f2','l2',888);

create table tableB
(   -- assuming this is really a user table
    id int auto_increment primary key, -- if you don't have this, you are going to have problems
    firstName varchar(150) not null,
    lastName varchar(150) not null,
    clientExtension int not null
);
insert tableB (firstName,lastName,clientExtension) values ('f1','l1',0),('f2','l2',0);

update tableB b
join tableA a
on a.id=b.id
set b.clientExtension=a.clientExtension;

select * from tableA;
(same as below)

select * from tableB;
+----+-----------+----------+-----------------+
| id | firstName | lastName | clientExtension |
+----+-----------+----------+-----------------+
|  1 | f1        | l1       |             777 |
|  2 | f2        | l2       |             888 |
+----+-----------+----------+-----------------+

The long and short of it is that if you join on names that change in one table and not another, you have problems. That is why you need a primary key that won't change (as opposed to when Bob becomes Robert again).

Also, if your tables are not user tables, then the PK of an int id is just as important. The id is used in other tables without de-normalized ideas of dragging firstName, lastName over as keys in those non-user Entity tables, if you will.

What do I mean by non-user Entity tables? Well I kinda just made that up, first phrase that came to my head. It is about data normalization, and concepts like 2nd and 3rd Normal Form. Let's say you have a paystub table. A row needs to be identified by PayeeId (that is your user id from above tables), and other info such as pay period, etc. A horrible way of identifying the Payee would be by first and last name.

Plan B: (I hold my nose at doing this, but here it is)

update tableB b
join tableA a
on a.firstName=b.firstName and a.lastName=b.lastName
set b.clientExtension=a.clientExtension;
-- 2 row(s) affected

Thanks so much for taking the time to create that post. The thing is, table B was exported from our operational database. I couldn't re-create Table B properly even if I wanted to, because I don't know the contraints and keys implemented throughout the operational database (for table B, as well as the other tables involved in the database). — Ephexx, Aug 26 '15 at 18:06
The primary goal here is to take the values from the `clientExtension` column in Table A, and somehow import these values into the same column `clientExtension` in Table B. This would need to happen `WHERE tableA.lastName=tableB.lastName AND tableA.firstName=tableB.firstName` because I am only updating 31 records in Table B, out of the total ~800 something. — Ephexx, Aug 26 '15 at 18:09
So when I execute that query (Plan B), I get the following error: `Table 'master.managers' doesn't exist`. I'm assuming this is because I'm attempting to update a value(s) in Table B that have Foreign Keys to a `Managers` table, within the operational DB? I only exported one table (Table B) from the entire operational Database, thus I do not have the `Managers` table in my personal-dev environment. — Ephexx, Aug 26 '15 at 18:23
`show create table tableName` will show it to you. This Answer covers the Question — Drew, Aug 26 '15 at 18:25

How do I insert a column of values from one table to another, non-matching schemas?

1 Answers1

Plan B: (I hold my nose at doing this, but here it is)