CODEWITHSUNDEEP
mysql
nonremovable
nonremovable

Reputation: 836

MySql: How to add a column from one table to that of another

I have a table of locations that are verified, like so:

+--------+----------+----------+
|  idW   |   lat    |   lon    |
+--------+----------+----------+
| 111650 | 47.20000 | 14.75000 |
| 111810 | 47.96412 | 16.25498 |
| 111820 | 47.83234 | 16.23143 |
+--------+----------+----------+

I also have a table of "all locations", whether verified or not. It looks like this (with lots of other columns I'm leaving out)

+--------+--------+----------+----------+
|   id   |  idW   |   lat    |   lon    |
+--------+--------+----------+----------+
| 100000 | 111650 | 47.20000 | 14.75000 |
| 100001 | 111712 | 42.96412 | 19.25498 |
| 100002 | 111820 | 47.83234 | 16.23143 |
+--------+--------+----------+----------+

What I would like to do is, for each verified location, find its "id" in the table of "all locations", and attach those as a new first column on the verified table (remembering that not all verified locations exist in all locations, so it's not as easy as copy and paste I don't think). Any ideas?

edit: The expected output from my example above would be

+--------+--------+----------+----------+
|   id   |  idW   |   lat    |   lon    |
+--------+--------+----------+----------+
| 100000 | 111650 | 47.20000 | 14.75000 |
| 100002 | 111820 | 47.83234 | 16.23143 |
|   x    | 111810 | 47.96412 | 16.25498 |
+--------+--------+----------+----------+

where x would be whatever value that 111810 had as its id in the all locations table.

Upvotes: 0

Views: 45

Answers (2)

dchar
dchar

Reputation: 1695

If I understand correctly you want to add a new column to your original table. This can be done as

ALTER TABLE locations ADD COLUMN `id` INTEGER NULL DEFAULT NULL FIRST;

and afterwards you can populate it by getting the values from the verified locations table as

SET SQL_SAFE_UPDATES = 0;
UPDATE locations a SET id = 
(SELECT id FROM verified_locations b 
WHERE a.idW = b.idW AND a.lat = b.lat AND a.`long` = B.`long`
LIMIT 1);

Upvotes: 1

Shadow
Shadow

Reputation: 34294

The better option would be to only display the additional data when you query the database using in joins either in a normal query or in a view.

select t1.*, t2.field1, t2.field2 from t1 inner join t2 on t1.idW=t2.idW

You can copy the data over to your 1st table (there are valid reasons to do it, eg. optimalization of selects, but itás a rare case). You need to add the extra columns to your first table using alter table add column ... commands (or just use an sql editor app).

Then to copy the data over:

update t1, t2 set t1.fieldname=t2.fieldname where t1.idW=t2.idW

Since adding columns to a table is not really efficient, you may choose to create a 3rd table from the existing ones and copy the data over:

create table t3 as select t1.*, t2.fieldname1, t2.fieldname2
from t1 inner join t2 on t1.idW=t2.idW

Upvotes: 1

Related Questions