Hello. There is a table in the database with 10k records. These data are taken from a third-party resource and periodically updated. So the question is: how to correctly update the data in the table, if each time comes a different number of records? That is more, it is less. So far, I am looking in the direction of TRUNCATE TABLE, but I suspect that this is the wrong decision.

  • And what% of the records actually change with the update? And if there are several third-party resources at the same time you cannot update all the data, they probably come in one resource and clear the entire table (for all resources) before loading is clearly redundant, what if some of the resources are temporarily not available, and you have already erased the data Mike
  • I think,% of the change records are around 20%, but there is no exact data on this. Theoretically, changes can be in 100% of the records. - Coder_D
  • but as I understand it, you still have to check or try to update each record, but you don’t know in advance exactly what has changed. And the main thing for you after all is to remove those that were not in the external source at the time of the update, right? And as a general update, en masse for all resources and it is guaranteed that they are all available or one by one. And what to do with the records if some resource was not available at the time of the update? - Mike
  • I was thinking of updating everything in a crowd, without additional checks, in order to save script execution time. Yes, the main thing is to delete extra entries. Updates come from a single resource. About the possible inaccessibility of the resource has not yet thought. But I think you can screw up some kind of accessibility check before starting the script. - Coder_D
  • Judging by the fact that TRUNCATE does not scare you, this table has no links. If so, I recommend importing to a new table, and if it went well (and the resource was available, and the data from it was received correctly and imported without any problems), then simply replace the old table with a new one (two renames, business .. .). At the same time, the old tables should not be deleted immediately (well, as something else is wrong!), But at least a couple of penultimate versions should be stored. - Akina

0