gierg gierg - 27 days ago 11
MySQL Question

MySQL Insert join table statement and update field on joined table

I have two table with column like this:

Table aggthndet (Reference table)

SELECT `aggthndet`.`idaggdet`,
`aggthndet`.`idagg`,
`aggthndet`.`noakun`,
`aggthndet`.`ketdet`,
`aggthndet`.`pagu`,
`aggthndet`.`prosesagg`,
`aggthndet`.`realisasi`,
`aggthndet`.`iu_id_usr`,
`aggthndet`.`iu_wkt`,
`aggthndet`.`iu_stat`FROM `aggthndet`;


Table aggakundet

SELECT `aggakundet`.`id`,
`aggakundet`.`idaggdet`,
`aggakundet`.`ketdetakun`,
`aggakundet`.`volume`,
`aggakundet`.`hrg_satuan`,
`aggakundet`.`iu_id_usr`,
`aggakundet`.`iu_wkt`,
`aggakundet`.`iu_stat`
FROM `aggakundet`;


The tables are mutually related to each other (relationship one-to-many)

i want insert data into table aggakundet, and update column pagu on table aggthndet, pagu column is the sum of the overall jml_total (alias column) of columns that have the same idaggdet.

sample data


table aggthndet

table aggakundet

Answer

In your script, you are having the value of idaggdet in $idaggdet.

Once insertion is done, proceed with UPDATE using the values in $idaggdet

You can take the below query as reference,

UPDATE `aggthndet`
SET `pagu` = `pagu`+1
WHERE `idaggdet` = '$idaggdet';

You can append this update statement in $sql itself.

Comments