CODEWITHSUNDEEP
mysql
gierg
gierg

Reputation: 37

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

Upvotes: 0

Views: 203

Answers (1)

Viki888
Viki888

Reputation: 2774

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.

Upvotes: 1

Related Questions