MySQL update col4 based on whether value from col1 exists in col2 and col3 = value

Question

I have a table (named units) with the below structure:

id     type     type_id     name     parent     hide
====================================================
12     child       2         no1-r     36        0
32     child       2         no2-l     0         0
36     parent      1         no1       0         0
42     parent      1         no4       0         0
59     child       2         no5-t     0         0
60     child       2         no6-r     72        0
63     child       2         no6-l     72        0
72     parent      1         no6       0         0
81     parent      1         no7       0         0
94     parent      1         no8       0         0
95     parent      1         no9       0         0
97     child       2         no9-r     95        0
99     child       2         no9-t     95        0

What I want to do is iterate through all id's and if id does not exist in parent and type_id = 1 then set hide to 1. So my output table would result as:

id     type     type_id     name     parent     hide
====================================================
12     child       2         no1-r     36        0
32     child       2         no2-l     0         0
36     parent      1         no1       0         0
42     parent      1         no4       0         1
59     child       2         no5-t     0         0
60     child       2         no6-r     72        0
63     child       2         no6-l     72        0
72     parent      1         no6       0         0
81     parent      1         no7       0         1
94     parent      1         no8       0         1
95     parent      1         no9       0         0
97     child       2         no9-r     95        0
99     child       2         no9-t     95        0

So basically because id's 42, 81 and 94 do not exist in parent their hide value is set to 1. Whereas, id 36 does exist in parent so isn't affected and also id 32 isn't affected even though it does not exist in parent because its type_id is 2.

I cannot for the life of me wrap my head around what MySQL (running ver 5.6.44) update to run on this table to achieve this output, so any help would be greatly appreciated!

Answer 1

You can do it with a self LEFT join in the UPDATE statement:

UPDATE units u1
LEFT JOIN units u2 ON u2.parent = u1.id
SET u1.hide = 1
WHERE u1.type_id = 1 AND u2.id IS NULL

See the demo.
Results:

> id | type   | type_id | name  | parent | hide
> -: | :----- | ------: | :---- | -----: | ---:
> 12 | child  |       2 | no1-r |     36 |    0
> 32 | child  |       2 | no2-l |      0 |    0
> 36 | parent |       1 | no1   |      0 |    0
> 42 | parent |       1 | no4   |      0 |    1
> 59 | child  |       2 | no5-t |      0 |    0
> 60 | child  |       2 | no6-r |     72 |    0
> 63 | child  |       2 | no6-l |     72 |    0
> 72 | parent |       1 | no6   |      0 |    0
> 81 | parent |       1 | no7   |      0 |    1
> 94 | parent |       1 | no8   |      0 |    1
> 95 | parent |       1 | no9   |      0 |    0
> 97 | child  |       2 | no9-r |     95 |    0
> 99 | child  |       2 | no9-t |     95 |    0