CODEWITHSUNDEEP
mysql
Jonas
Jonas

Reputation: 133

Copy values from one table to another where same ID

I am working with a table of 1000+ rows where data in two columns has been corrupted (table_corrupted). Luckily I have an outdated backup of that table where these two columns are intact (table_outdated). So I thought: Why not just replace the values in those two columns and leave the rest as it is?

Let's say table_corrupted & table_outdated both have 5 columns:

id(INT), name(TEXT), lat(DOUBLE), lon(DOUBLE), comment(TEXT)

insert into `table_corrupted` (`lat`,`lon`) 
select `lat`,`lon` from `table_outdated`
WHERE `table_corrupted`.`id` = `table_outdated`.`id`;

...results in this error: "Unknown Column 'table_corrupted.id'In Where Clause"

After some research I found out this is because SQL is evaluated backwards, from right to left. To be honest, I did not figure out a solution - any advice? What am I doing wrong?

Upvotes: 1

Views: 4580

Answers (3)

Rahul Tripathi
Rahul Tripathi

Reputation: 172398

You can better join the tables and simply update the values in your corrupted table by executing the below query

update `table_corrupted`
inner join `table_outdated` on `table_corrupted`.`id` = `table_outdated`.`id`
set `table_corrupted`.`lat`= `table_outdated`.`lat`,
`table_corrupted`.`lon`= `table_outdated`.`lon`

Upvotes: 5

Giles
Giles

Reputation: 1667

You can use ON DUPLICATE:

insert into `table_corrupted` (`id`,`lat`,`lon`) 

    select `id`,`lat`,`lon` from `table_outdated`
    on duplicate key update table_corrupted.lat = table_outdated.lat, table_corrupted.lon = table_outdated.lon

or an update:

update table_corrupted,table_outdated
set table_corrupted.lat = table_outdated.lat, table_corrupted.lon = table_outdated.lon
where table_corrupted.id = table_outdated.id

Upvotes: 1

Akarsh Vijayan
Akarsh Vijayan

Reputation: 271

Dont use insert. Use update. This one worked for me.

UPDATE `table_corrupted` INNER JOIN `table_corrupted` ON (`table_corrupted`.`id` = `table_outdated`.`id`) 
SET `table_corrupted`.`lat` = `table_outdated`.`lat`, `table_corrupted`.`lon` = `table_outdated`.`lon`

Upvotes: 1

Related Questions