SQL query syntax error, UPDATE statement with INNER JOIN

Question

my question should be very easy to fix but I can't find the syntax error that my db interface throws at me. The following code is used to update the password of an existing user in a timetrackingtool.

I checked all the tables and field names, they all are right.

The SQL query code:

$query = ('UPDATE tblMitarbeiterUUID SET tblMitarbeiterUUID.dtPassword="' . $pwd . '" '.
          'INNER JOIN arbeiter '.
          'ON tblMitarbeiterUUID.idMitarbeiterUUID=arbeiter.fidMitarbeiterUUID ' .
          'WHERE arbeiter.id=' . $userID)

Echo of this query:

UPDATE tblMitarbeiterUUID SET tblMitarbeiterUUID.dtPassword="7687225fde7aad38f4c005ad4b5cdd5a" INNER JOIN arbeiter ON tblMitarbeiterUUID.idMitarbeiterUUID=arbeiter.fidMitarbeiterUUID WHERE arbeiter.id=1

Error I get when trying to run the query directly on the db:

SQL error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM tblMitarbeiterUUID INNER JOIN arbeiter ON tblMitarbeiterUUID.ifMitarbeiterUUID=arbeiter.fidMitar...' at line 1

In my research I saw some people using a FROM in a UPDATE statement with an INNER JOIN. I tried it with a FROM but I get the same error as above.

Thanks for your help in advance. Best Regards, Luca.

correct syntax (edited after I got the correct answer):

'UPDATE tblMitarbeiterUUID x '.
'JOIN arbeiter y ON x.idMitarbeiterUUID = y.fidMitarbeiterUUID ' .
'SET x.dtPassword="' . $pwd . '" ' .
'WHERE y.id=' . $userID

Note also that you're open to sql injection

Strawberry
– Strawberry

2018-07-09 06:52:58 +00:00
Commented Jul 9, 2018 at 6:52 — Strawberry
– Strawberry, Commented Jul 9, 2018 at 6:52

Strawberry · Accepted Answer · 2018-07-09 06:56:02Z

3

This is valid syntax:

UPDATE tblMitarbeiterUUID x

JOIN arbeiter y
ON x.idMitarbeiterUUID = y.fidMitarbeiterUUID 

SET x.dtPassword="A"  

WHERE y.id=1

answered Jul 9, 2018 at 6:56

Strawberry

34k14 gold badges43 silver badges57 bronze badges

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

1 Comment

Luca Over a year ago

Thanks alot this was the answer I was looking for.

Collectives™ on Stack Overflow

SQL query syntax error, UPDATE statement with INNER JOIN

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related