Copying a table to an existing table

Good morning, I’m trying to copy data from an sql table(tb2) into another sql table (tb1) and have the tb2 data append with the row in tb1 by the ID. They are in the same DB. This will only be a one time thing I won’t have to do this recurring. The table structures are as follows:



I have the following code which will copy all the data but adds it to the end of the table instead of matching the ID’s and placing them accordingly.

INSERT INTO Tb1 (state, country)
SELECT state,country
FROM tb2

If I add a WHERE statement I receive the error “unknown column” in “WHERE clause”


Any help would be greatly appreciated, thank you.

Do you mean id or ID? In your table structures you are using all caps but in your query you are using lowercase letters.

Number one, why would you insert into table1 if it’s already there? That would give you duplicates.

I think you’re looking for something more like

INSERT INTO Tb1 (state, country)
SELECT Tb2.state
  FROM Tb2
  LEFT JOIN Tb1 AS Missing ON =

basically I’m trying to consolidate the tables. I’ll give the new code a try as soon as my system finishes updating. thx

unfortunately that didn’t work. it came back with ‘0 rows inserted’ and didn’t update or add data to tb1.

It didn’t add records because you have the same ids on both tables. If you’re looking to update, that’s another situation all together (INSERT != UPDATE)

If you’re on mySQL, you can try ON DUPLICATE INSERT, but I don’t know if you can do that using a SELECT. For sanity’s sake, I’d split them into two transactions, but that’s just me…

actually you have a good point I am actually trying to update tb1 with the data from tb2 coinciding with their relative id’s. sorry for the confusion. that being said I’ve played around with a few UPDATE querys and still can’t get it to work. any ideas? thank you.

Ok I figured it out. What I came up with was the following.

UPDATE tb1, tb2
SET =,
tb1.state = tb2.state

it doesn’t add the data from tbl2 if there’s no id for tb1 but that’s fnie for now. Thanks for all the help I appreciate it.

That’s what the first query I gave you should have done…

It was my fault for saying it was an insert instead of an update. I actually used a great deal of what you had sent me in order to figure it out. Thank you again for the help.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.