CODEWITHSUNDEEP
mysql
victorj
victorj

Reputation: 45

Insert id's from one table to another based on another column

New MySql user here.

Suppose I have two tables:

Table1 rownames -> external_id, name, location

Table2 rownames -> id, name, type, value, year

What I want to do is to write a script where it populates in Table2.id the value from Table1.external_id, if there is a match in Table1.name and Table2.name.

I thought about something like this, but unfortunately its not working.

insert into Table2.id
    select Table1.external_id
    where Table1.name = Table2.name

Upvotes: 1

Views: 68

Answers (2)

e4c5
e4c5

Reputation: 53734

If you do an insert here, what you will do is create a whole bunch of duplicates. What you need is an update join

UPDATE table1 INNER JOIN table2 set table2.id = table1.external_id
WHERE Table1.name = Table2.name;

You don't need any script for this, just type this into the mysql client, phpmyadmin etc

Upvotes: 1

Bernd Buffen
Bernd Buffen

Reputation: 15057

try this:

insert into Table2 (id)
    select Table1.external_id
    where Table1.name = Table2.name;

Upvotes: 1

Related Questions