SQL Join one tables data to anothers

Question

So, I am currently using PHPmyadmin 4.7.7 and I am trying to essentially Update table A's "Latitude" column with the values i've stored in another table, which I had imported from a CSV. I've been looking around at what people have been doing but I can't seem to get it to work.

If i'm correct in thinking I need to do something like the following:

UPDATE q
SET q.Latitude = a.Latitude
FROM geodata q
INNER JOIN geotemp a
ON q.Latitude = a.Latitude
WHERE q.gridref = a.gridref;

but this gives me the following error:

#1064 - You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use 
near 'FROM
geodata q
INNER JOIN geotemp a ON q.Latitude = a.Latitude
WHERE
q' at line 5

I've looked up the Syntax for inner join but can't see an issue with what i've put in, it still happens if I get rid of q and a and use "geodata" and "geotemp" Any advice?

My desired outcome here is for geodata to have geotemps Latitude data on existing records where a "gridref" column matches.

Thanks

You can't use a `JOIN`statement in an `UPDATE`without using a subquery — schlonzo, Apr 16 '18 at 13:35
@schlonzo Oh yes you can: https://stackoverflow.com/questions/15209414/how-to-do-3-table-join-in-update-query — Tim Biegeleisen, Apr 16 '18 at 13:36
@schlonzo Good call, I don't see a sample query there either. _But_, I do find the language `For the multiple-table syntax, UPDATE updates rows...`. In other words, it is discussing what happens for multi-table updates, hence it admits that they exist. Welcome to the world of open source, no guarantees that anything is explained properly, but hey that's why we have Stack Overflow :-) — Tim Biegeleisen, Apr 16 '18 at 13:47
@TimBiegeleisen Thank you for your help! Where has my comment gone? But it's a nice way of doing an `UPDATE` — schlonzo, Apr 16 '18 at 13:49
@schlonzo Someone must have flagged you for some reason, and the moderator removed it. You are leaving comments not directly relevant to the question, under someone else's question. Don't do that in general. — Tim Biegeleisen, Apr 16 '18 at 13:50

score 2 · Accepted Answer · answered Apr 16 '18 at 13:35

2

In MySQL's update join syntax, the SET clause comes immediately after the join, not after the UPDATE clause.

UPDATE q
FROM geodata q
INNER JOIN geotemp a
    ON q.Latitude = a.Latitude
SET q.Latitude = a.Latitude
WHERE q.gridref = a.gridref;

answered Apr 16 '18 at 13:35

Tim Biegeleisen

502,043
27
286
360

Thanks, this worked a charm! Very useful thing to know! – Henry Apr 16 '18 at 15:18

Jay Shankar Gupta · Answer 2 · 2018-04-16T14:38:48.917

1

UPDATE geodata q
INNER JOIN geotemp a
ON q.Latitude = a.Latitude
SET q.Latitude = a.Latitude
WHERE q.gridref = a.gridref;

For general Update join :

UPDATE TABLEA a 
JOIN TABLEB b 
ON a.join_colA = b.join_colB  
SET a.columnToUpdate = [something]
WHERE ....

edited Apr 16 '18 at 14:38

answered Apr 16 '18 at 13:39

Jay Shankar Gupta

5,918
1
10
27

SQL Join one tables data to anothers

2 Answers2