CODEWITHSUNDEEP
mysqlsql
themerlinproject
themerlinproject

Reputation: 3582

mysql update column inner join another table

I'm trying to update a field based on a field of another table. Here is the code:

UPDATE h 
SET h.strength = c.strength 
FROM hesters AS h 
INNER JOIN campers AS c 
ON h.camper_id = c.id

Getting "#1064 - You have an error in your SQL syntax;"

I'm basing my code off this answer here.

Anyone spot the syntax error?

Upvotes: 0

Views: 445

Answers (3)

majidarif
majidarif

Reputation: 19985

Try doing something like:

UPDATE hesters AS h 
INNER JOIN campers AS c 
ON h.camper_id = c.id
SET h.strength = c.strength

update

This works on sqlfiddle.

Upvotes: 0

themerlinproject
themerlinproject

Reputation: 3582

I don't know why the code from the previous linked answer didn't work, but here is what I ended up going with, from the mysql documentation on UPDATE (search for "join").

UPDATE hesters AS h,campers AS c 
SET h.strength = c.strength 
WHERE h.camper_id = c.id

Upvotes: 1

wils484
wils484

Reputation: 275

You need to place your JOIN clause before your SET clause, and your h alias needs to be set at the beginning:

UPDATE hesters AS h 
INNER JOIN campers AS c 
ON h.camper_id = c.id
SET h.strength = c.strength

Upvotes: 0

Related Questions