MySQL extracts data from one table update another table (fix inconsistent table data)

Currently encountering a data inconsistency, there are two tables, a project table, a project member table, the Project table has a field is the project work time, is the project member's working time summary. It's because of the logic, so change the data to be consistent.

The approximate structure of the project table is as follows.

Table Name: Project

The Project members table is broadly structured as follows.

Table Name: Projectmember

The PID of the Projectmember table is associated with Project ID, and Project_worktime is member_worktime.

Insert three data into Project table.

Projectmember table data is as follows:

The SQL statements are as follows:

UPDATE Project SET Project.project_worktime=(SELECTSUM(member_worktime)                                from projectmember                              WHERE projectmember. PID=project. ID)

Execution Result:

Modify the Projectmember table the third data is 20.

Execute the SQL statement once:

Note the rows affected, the first is 3, because 3 data is updated, the second is 1, and only one of the data in the Projectmember table that corresponds to the project table is modified. Therefore, there is no repeat update, only the changed data is updated.

Modify the Projectmember table second to third data to execute the SQL statement again, the affected rows are still one.

Modifying the third to fourth data to execute the SQL statement again, the affected rows become two.

MySQL extracts data from one table update another table (fix inconsistent table data)