I don’t expect anyone to know this, but I’m writing it to work it out a little bit.
In a SQL database (in this case mySQL accessed via PHP), which way is better when trying to update a set of related records:
a)
– DELETE all records which have major key
– INSERT with the changed data for each minor key
b)
– SELECT based on minor key for each record
– INSERT if record with key not found and SELECT to get the new minor key
– UPDATE if record is found and there are changes
– DELETE all record with major key if not one of the selected minor keys
If everything else is even, you want A if you do a lot of changes and B if you only have a few, but I guess the real question is if everything else is even; do different SQL query types require more or less of the system?
This shouldn’t really bug me, but it’s not a situation where the database is gonna get hit a lot to begin with, but still, I wish I knew what was the right way.