SQL Syntax error on JOIN

Question

MY TABLES:

        USERS_1:                             USERS_2:
   +------------+---------+        +------------+---------+
   |    id      |username |        |  username  |claimedBy| 
   +------------+---------+        +------------+---------+
   |     4      | pitiqu  |        | myUsername |  NULL   |<- this should become 4
   +------------+---------+        +------------+---------+

MY SQL: (Literally MySQL)

UPDATE UL
SET UL.claimedBy = US.username
FROM USERS_1 as UL
INNER JOIN USERS_2 as US
ON US.id = 4
where UL.username="myUsername"

It's probably obvious that i want to set table 2's claimed_by (for the username "myUsername") to the username "pitiqu" found in table 1 at id = 4.

I'm sorry if all the "username" is confusing. Hope the tables and the SQL clears my question.

The error that pops out:

#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 USERS_1 as UL INNER JOIN USERS_2 as US ON US.id = 4 where UL' at line 3

Why is this happening... anyone?

EDIT : Excuse me for the incorrect syntax. I've been trying to use THIS example and while editing it I deleted the SET.

score 3 · Accepted Answer · answered Jan 06 '16 at 13:32

3

You could use a update query like this:

update
  USERS_2
set
  claimedBy = (SELECT username FROM USERS_1 WHERE id=4)
where
  username="myUsername"

if you want a join, the correct syntax is like this however on this particular context it doesn't make much sense and I would suggest you to use the first query:

UPDATE
  USERS_1 as UL INNER JOIN USERS_2 as US ON US.id = 4
SET
  UL.claimedBy = US.username
WHERE
  UL.username="myUsername"

answered Jan 06 '16 at 13:32

fthiella

48,073
15
90
106

This is what I've been looking for. The first example I was aware of but my purpose is to use a JOIN keyword for now so I'll stick to the second example. Thank you. – Razvan M. Jan 06 '16 at 13:38

score 2 · Answer 2 · answered Jan 06 '16 at 13:34

2

That's a wrong syntax. You should use a update join like

UPDATE UL u
JOIN USERS_2 US ON US.id = 4
SET u.claimedBy = US.username
where u.username='myUsername';

answered Jan 06 '16 at 13:34

Rahul

76,197
13
71
125

Sarath Chandra · Answer 3 · 2016-01-06T13:45:26.583

1

You're using FROM in an UPDATE query. That is downright incorrect.

One way of rewriting it would be as below, making use of a subquery:

UPDATE USERS_2 set claimedBy = (SELECT id from USERS_1 where username = "pitiqu")
where username="myUsername";

edited Jan 06 '16 at 13:45

answered Jan 06 '16 at 13:31

Sarath Chandra

1,850
19
40

Oh that's embarrasing but still... I'm going to edit it with a proper SET and it still won't work. This is something I just took from the internet to "showcase" my problem. – Razvan M. Jan 06 '16 at 13:33

SQL Syntax error on JOIN

3 Answers3