Why did I get this MySql error while updating sql

Question

I have a table like.

CREATE TABLE `chart` ( `id` int(10) NOT NULL AUTO_INCREMENT, `item` char(6) NOT NULL DEFAULT '', `IsLeaf` char(1) NULL DEFAULT 'Y', `ParentId` int(10) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) ) 

Where parentId contains the identifier of another row, which is the parent of this row How

 ----------------------------------------------------------------- | Id | item | IsLeaf | ParentId ----------------------------------------------------------------- | 1 | Test1 | D | ----------------------------------------------------------------- | 2 | Test3 | D | ----------------------------------------------------------------- | 3 | Test4 | D | 1 ----------------------------------------------------------------- | 4 | Test5 | D | 1 ----------------------------------------------------------------- 

I want to update those rows that have at least one child row. I tried like this

 UPDATE chart AS c1 SET c1.IsLeaf='Y' JOIN chart c2 ON c2.ParentId=c1.id; 

and got this error

 [Err] 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 'JOIN chart c2 ON c2.ParentId=c1.id' at line 1 

Answer 1

You need to use SET KEYWORD after LEFT JOIN and check for null

 UPDATE chart AS c1 LEFT JOIN chart c2 ON c2.ParentId=c1.id SET c1.IsLeaf='Y' WHERE c2.id is null; 

Answer 2

Try using the LEFT JOIN as follows: -

  UPDATE chart AS c1 LEFT JOIN chart c2 ON c2.ParentId=c1.id SET c1.IsLeaf='Y' WHERE c2.id is null;