UPDATE and JOIN causing a Syntax Error in MySQL

Question

UPDATE default_weekly_stats s 
INNER JOIN default_profiles p
ON p.user_id = s.user_id
WHERE (default_profiles.opid = 0 OR default_profiles.opid IS NULL) 
    AND s.week = `1`
    AND s.correct_picks = `4`
SET s.rank = 1

That's my query and I'm getting an error:

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 'WHERE (default_profiles.opid = 0 OR default_profiles.opid IS NULL) AND s.week = ' at line 1

The syntax looks right to me, but clearly I'm missing something. Any ideas what?

have you tried leaving out the backticks?

GOTO 0
– GOTO 0

2013-09-06 15:10:07 +00:00
Commented Sep 6, 2013 at 15:10 — GOTO 0
– GOTO 0, Commented Sep 6, 2013 at 15:10

juergen d · Accepted Answer · 2013-09-06 15:11:08Z

3

WHERE comes after SET

UPDATE default_weekly_stats s 
INNER JOIN default_profiles p ON p.user_id = s.user_id 
SET s.rank = 1
WHERE (default_profiles.opid = 0 OR default_profiles.opid IS NULL) 
AND s.week = 1 
AND s.correct_picks = 4

And as @Chad mentioned: Leave the backticks. You could use quotes but don't need any delimiter for numbers.

answered Sep 6, 2013 at 15:11

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tricky12 Over a year ago

He also does not need backticks if rank, week and correct_picks are of type int.

Hamlet Hakobyan · Accepted Answer · 2013-09-06 15:12:37Z

3

Look at the UPDATE Syntax

Try this:

UPDATE
  default_weekly_stats s
    INNER JOIN default_profiles p
      ON p.user_id = s.user_id 
SET s.rank = 1
WHERE (default_profiles.opid = 0 OR default_profiles.opid IS NULL)
  AND s.week = `1`
  AND s.correct_picks = `4`

answered Sep 6, 2013 at 15:12

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

Collectives™ on Stack Overflow

UPDATE and JOIN causing a Syntax Error in MySQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related