How to make an UPDATE of records to 3 related tables?

Question

How to make an UPDATE of records to 3 related tables?

Navigation

#1 by (2 votes)

1

I have three related tables as follows:

Now what I have to do is edit the information of a course, for example, the start date and in the quota table edit the amount of fees, to later edit the number of amounts in the table detalle_cuota. It is an update to the 3 related tables, but I do not know how to handle the update, I guess it is through the id_primary but I'm a bit lost. All these data I bring them in a form and then edit them, for example I want to change the start date that is: 08-26-2017 to 08-30-2017, edit the amount of course fees, have 4 installments now I want put 6 installments, and according to that amount of fees I updated the amounts in the table detalle_cuota, if they were in 4 that are now 6. I need guidance please! The only thing that occurs to me is to do 3 update and go retrieving the id that updates in each update, but I do not know if it works.

mysql php

asked by 26.08.2017 в 21:48

source

1 answer

Update of multiple rows with different values RelatedObjectDoesNotExist User has not profile

score 2 · Answer 1

I would focus the solution on 1 update, 1 delete and 1 insert. Let me explain:

UPDATE cursos_abiertos SET inicio = '30-08-2017' WHERE id = <id_curso>

Then I would eliminate the quotas that are associated with the course, and then reschedule the payments (here I assume that you have a DELETE ON CASCADE that will clean the table detalle_cuota ):

DELETE FROM cuotas c 
WHERE id_curso = <id_curso> 
AND EXISTS (SELECT 1 FROM detalle_cuota WHERE id_curso = c.id_curso AND estado = 0)

We calculate the total amount to be replanned

SELECT SUM(monto)/<nuevo numero de cuotas>
FROM detalle_cuota dc 
INNER JOIN cuotas c
ON c.id = dc.id_curso
WHERE c.id_curso = <id_curso>

INSERT INTO cuotas (...) VALUES (...),(...),(...) /* nuevas cuotas */

INSERT INTO detalle_cuotas (...,monto,...) VALUES (...,<monto por cuota>,...)

In summary, take a bit of care in linking the data, but much more robust than updating IDs.

I hope I have helped you.