update with self relationship

Question

I have a table called Category, in it I have the attributes ID, DESCRICAO e ORIGEM, I needed to include one more attribute ID_CATEGORIA_PAI, this attribute is an autorelationation with the same table. Now I need to take some tuples from this table and inform the ID of the parent category, the daughter categories registered nowadays have the same description of the parent category, but with the ending ". 1", ". 2" and so on and with the origin "son". Today it’s like this:

CAT_ID | CAT_DESC       | CAT_ORIGEM | CAT_ID_PAI
1      | Categoria A    | Cadastro   | null
2      | Categoria B    | Cadastro   | null
3      | Categoria C    | Cadastro   | null
4      | Categoria A.1  | Filho      | null
5      | Categoria B.1  | Filho      | null
6      | Categoria C.1  | Filho      | null
7      | Categoria A.2  | Filho      | null
8      | Categoria B.2  | Filho      | null
9      | Categoria C.2  | Filho      | null

I need to get the following result:

CAT_ID | CAT_DESC       | CAT_ORIGEM | CAT_ID_PAI
1      | Categoria A    | Cadastro   | null
2      | Categoria B    | Cadastro   | null
3      | Categoria C    | Cadastro   | null
4      | Categoria A.1  | Filho      | 1
5      | Categoria B.1  | Filho      | 2
6      | Categoria C.1  | Filho      | 3
7      | Categoria A.2  | Filho      | 1
8      | Categoria B.2  | Filho      | 2
9      | Categoria C.2  | Filho      | 3

Can anyone tell me if it is possible to make an update script to update these child categories based on the category description and origin?

Answer 1

Yes, it is possible! Considering the expected output example, you need to validate whether the item will be updated (CAT_ORIGEM) is the type "Son" And if there’s one of those "Register" that it’s your start, like this:

UPDATE cadastro c
SET CAT_ID_PAI = (SELECT c2.CAT_ID 
                  FROM cadastro c2 
                  WHERE c.CAT_DESC LIKE CONCAT(c2.CAT_DESC, '%')
                    AND c2.CAT_ORIGEM = 'Cadastro'
                  LIMIT 1)
WHERE c.CAT_ORIGEM = 'Filho'

---

Since you didn’t specify the bank, I created this example in mysql; the query is a little different because I used a join, but the idea follows the same:

UPDATE categoria c
JOIN categoria c2 on c.CAT_DESC LIKE CONCAT(c2.CAT_DESC, '%')
SET c.CAT_ID_PAI = c2.CAT_ID
WHERE c.CAT_ORIGEM = 'Filho';