0

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?

Community
  • 1
  • 1
themerlinproject
  • 3,542
  • 5
  • 37
  • 55

3 Answers3

1

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
themerlinproject
  • 3,542
  • 5
  • 37
  • 55
  • 1
    That's because the linked answer belongs to SQL Server, **not** MySQL (which is what you're using). No two RDBMS vendor implements features in exactly the same way. – Clockwork-Muse Apr 18 '14 at 02:34
0

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.

majidarif
  • 18,694
  • 16
  • 88
  • 133
0

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
wils484
  • 275
  • 1
  • 3
  • 14