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.

fthiella · Accepted Answer · 2016-01-06 13:32:16Z

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 6, 2016 at 13:32

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

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

1 Comment

Razvan M. Over a year ago

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.

Rahul · Accepted Answer · 2016-01-06 13:34:24Z

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 6, 2016 at 13:34

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Comments

Sarath Chandra · Accepted Answer · 2016-01-06 13:45:26Z

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 6, 2016 at 13:45

answered Jan 6, 2016 at 13:31

Sarath Chandra

1,87621 silver badges40 bronze badges

1 Comment

Razvan M. Over a year ago

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.

Collectives™ on Stack Overflow

SQL Syntax error on JOIN

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related