Copying a table to an existing table

Databases
1

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:

**Tb1**
ID
Name
address

**Tb2**
ID
State
Country

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 tb1.id” in “WHERE clause”

WHERE Tb1.id = Tb2.id

Any help would be greatly appreciated, thank you.

2

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

3

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
     , Tb2.country
  FROM Tb2
  LEFT JOIN Tb1 AS Missing ON Tb2.id = Missing.id
 WHERE Missing.id IS NULL
4

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

5

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

6

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…

7

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.

8

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

UPDATE tb1, tb2
SET tb1.country = tb2.country,
tb1.state = tb2.state
WHERE tb1.id = tb2.id

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.

9

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

10

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.

11

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