I'm a bit stuck with this, to see if someone can give me a hand.
On one side I have a table called liturgia_horas_lpatristicas
. In that table I have a named column id_liturgia
with values like this: 0708310401
. The goal is to normalize that value, which I originally thought of as some sort of combined code where 07 representa una cosa,
08 otra cosa,
31` something else and so on.
To do this, I created a new column with no data in that table called id_breviario
.
On the other hand I created a new table called liturgia_breviario
with the columns: id_breviario, id_tiempo, id_ciclo, id_semana, id_dia
.
The normalization is that I have to update the column id_breviario
of liturgia_horas_lpatristicas
with the corresponding value in liturgia_breviario
.
I show an example of the data:
In liturgia_horas_lpatristicas
I have data like this:
id_patristica id_liturgia id_breviario
-----------------------------------------------
76589 0708310401 NULL
76590 0708310501 NULL
And in liturgia_breviario
I have rows like this:
id_breviario id_tiempo id_ciclo id_semana id_dia
----------------------------------------------------------------------
1 7 8 31 4
2 7 8 31 5
3 7 8 31 6
Expected result
id_patristica id_liturgia id_breviario
-----------------------------------------------
76589 0708310401 1
76590 0708310501 2
There are many rows, so you would need one UPDATE
that works through JOIN , poniendo en cada fila el
id_brevisrio que corresponda. El criterio sería determinar los valores de join basándose en subtrings de la columna
id_liturgia`, something like this:
SUBSTRING(id_liturgia,1,2) t,
SUBSTRING(id_liturgia,3,2) c,
SUBSTRING(id_liturgia,5,2) s,
SUBSTRING(id_liturgia,7,2) d
There I would have t,c,s,d
the values that I could use as criteria to find the id_breviario
in the table liturgia_breviario
, but I really don't know how to build the query so that it updates each row.
The UPDATE schema based on another table I know of is this:
UPDATE liturgia_horas_lpatristicas t2,
(SELECT id_breviario FROM liturgia_breviario WHERE -- criterios de t1 ) t1
SET
t2. id_breviario = t1. id_breviario
WHERE -- criterios de t2;
But this does not make a JOIN
dynamic to update each value with its corresponding one.