SQL UPDATE Field under certain conditon-CodePudding

I have a table like this:

product_number    active    parent_id
119183            1         0x975c4deb281d4befbe679dacb4a0fda0
119182            1         0x975c4deb281d4befbe679dacb4a0fda0
119181            1         0x975c4deb281d4befbe679dacb4a0fda0
119180            1         0x975c4deb281d4befbe679dacb4a0fda0
ATR-38496         1         0x975c4deb281d4befbe679dacb4a0fda0
119179            1         0x975c4deb281d4befbe679dacb4a0fda0
ATR-38473         1         0x0002252627424031b2968e02b78dcf0c

And i want to Update the table to this result:

product_number    active    parent_id
119183            1         0x975c4deb281d4befbe679dacb4a0fda0
119182            1         0x975c4deb281d4befbe679dacb4a0fda0
119181            1         0x975c4deb281d4befbe679dacb4a0fda0
119180            1         0x975c4deb281d4befbe679dacb4a0fda0
ATR-38496         0         0x975c4deb281d4befbe679dacb4a0fda0
119179            1         0x975c4deb281d4befbe679dacb4a0fda0
ATR-38473         1         0x0002252627424031b2968e02b78dcf0c

Important to know is, that this table has multiple products where each product may have some variants.
A product and his coresponding variants are listet under the same parent_id while each product and variant set has a different parent_id.
The main product needs active to be set to 0 and can be identified by the prefix "ATR-" on product_number.
Sadly i cant come up with a sql statement that works for that matter.

EDIT: I have some products that are standalone products and so dont have multiple products under the same parent_id and should not be set to active=0

CodePudding user response：

It seems that you need in

UPDATE table t1
  JOIN table t2 USING (parent_id)
SET t1.active = 0 
WHERE LEFT(t1.product_number, 4) = 'ATR-'
  AND t2.product_number <> t1.product_number;