JOIN in UPDATE

Databases
1 
[b]myTable2
(id) o[/b]
(1) 2
(2) 3
(3) 2
(4) 5

[b]myTabl3
(id) cate[/b]
(1) 1
(2) 2
(3) 1
(4) 1

I have 2 tables like the above.
The code1 below which has JOIN produces the result2 below.

[b]code1[/b]


SELECT myTable2.id, o
FROM myTable2
LEFT JOIN myTable3 ON myTable2.id=myTable3.id

WHERE
myTable3.cate=1

ORDER BY myTable2.id

[b]result1[/b]
(1) 2
(3) 2
(4) 5

In order to use the JOIN in UPDATE, I made the following would-be code which actually causes a SQL syntax error.

[b]would-be code[/b]
UPDATE myTable2.
LEFT JOIN myTable3 ON myTable2.id=myTable3.id

SET o = 1

WHERE
myTable3.cate=1

[b]target myTable2 data after updating [b]
(1) [COLOR="#FF0000"]1[/COLOR]
(2) 3
(3) [COLOR="#FF0000"]1[/COLOR]
(4) [COLOR="#FF0000"]1[/COLOR]

How can I make it work correclty without the SQL syntax error?

2

are you going to make us guess what the syntax error is?

would it have anything to do with that period stuck on the end of the table name?

3

Yes, the period was the problem.
Shame on me…

4

Try this code:

UPDATE myTable2
SET o = 1
WHERE id in (select id from myTable3 where myTable3.cate=1)