CODEWITHSUNDEEP
mysqlsqlphpmyadmin
Henry
Henry

Reputation: 25

SQL Join one tables data to anothers

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

Upvotes: 0

Views: 37

Answers (2)

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

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 ....

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522396

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;

Upvotes: 2

Related Questions