CODEWITHSUNDEEP
mysqlmerge
Bry
Bry

Reputation: 11

Mysql merge w tables with common column

I have:

table1:

Id | Name

table2:

Id | Amount

I want create a new table based on the common Id. So if a record from table1 and table2 have matching id, then:

table3

Id | name | Amount

Sorry if this has been asked before. I'm new to this and just want to get this done

Upvotes: 1

Views: 421

Answers (5)

Daniele Murer
Daniele Murer

Reputation: 247

If you want to merge the Name column of table1 in table2, using the common column Id, you can do this by running:

ALTER TABLE table1 ADD Name <dataTypeOfName>;
UPDATE table1, table2 SET table2.Name = table1.Name WHERE table1.Id = table2.Id;

This will create a new column called Name in table2 and then fill it with the values from table1, after performing a join based on the column Id.

Upvotes: 0

Bry
Bry

Reputation: 11

update:

I'd like to merge the tables based on the common column "id" and NOT create a 3rd table. Just add the "name column" and populate it where the id's are =

so i have:

table1:

Id | Name

table2:

Id | Amount

I would like the result to be

table2

Id | Amount | Name

Upvotes: 0

Maksym
Maksym

Reputation: 4574

Like this :

CREATE TABLE tavle_xxx(
id xxx,
name xxx,
amount xxx
);

INSERT IGNORE INTO tavle_xxx
SELECT t1.id, t1.name, t2.Amount
FROM table1 t1, table2 t2
WHERE t1.id=t2.id;

Upvotes: 0

Grasshopper
Grasshopper

Reputation: 152

Try using a JOIN

SELECT table1.id, table1.name, table2.amount
FROM table1
LEFT JOIN table2
ON table1.id=table2.id;

I didn't test this but I think it should work.

http://www.w3schools.com/sql/sql_join_left.asp

Upvotes: 0

Ruby van Soelen
Ruby van Soelen

Reputation: 145

Why wouldn't you do this with a simple select statement?

SELECT a.id, a.Name, b.Amount
FROM table1 a, table2 b
WHERE a.id = b.id

Upvotes: 1

Related Questions