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

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??

Solution

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