How to remove subquery?

Question

MySQL

There is a request:

UPDATE `table` SET `category` = '0' WHERE `category` = '1' AND `id` NOT IN (SELECT `id` FROM `table` WHERE `category` = '0')

That is, if possible, you need to change the category from 1 to 0, but only for those records where the category is not equal to 0, so that there are no duplicates.

The table consists of id and category, the PRIMARY index into two columns. The variant which approaches to the majority of SQL a DB is necessary. I thought with FULL OUTER JOIN, but MySQL does not support this, with UNION it will be slow.

Answer 1

Two field index -> unique and update ignore

Answer 2

Either I didn't understand or

UPDATE `table` SET `category` = '0' WHERE `category` = '1' 

what you need

Answer 3

Something, in my opinion, you overdid it, WHERE `category` = '1' so cuts off those records where `category` = '0'
is enough UPDATE `table` SET category = 0 WHERE category = 1