Mysql – Updating an existing SQL Database with thousands of records

MySQLupdate

I am having some trouble thinking of how to update an existing table. There are currently 1500 items in it and the structure of the table is like this:

Old:

ID Manager ID Manager Name Director Regional Director State Manager Head Manager
22 "tom123" "Tom Hawkins" "Jerry Lewis" "Mary Brauhm" "Anna Michaelson" "Jack Edwards"

New:

ID Manager ID Manager Name Director Regional Director State Manager Head Manager
22 "tom123" "Tom Hawkins" "Dennis Martin" "Mary Brauhm" "Nicholas Andrew" "Jack Edwards"

Say a state manager has left the company and now all items need to be updated. But while we're at it, we also want to correct and update the managers who are no longer with the company.

I know that we could update where stateDirector = "Anna Michaelson", but what if not all people formerly under Anna are assigned under the New Director? We have all these details in a seperate(current) file(excel, csv).

I know the structure of the table itself could be improved (also, been trying to think of this improvement), but what is the correct approach to updating all these if multiple IDs (employees) can exist under a manager.

Best Answer

Plan on doing one UPDATE per change needed. It might look like

UPDATE t SET president = 'Biden' WHERE president = 'Trump';

If someone has left the company but has not yet been replaced, that gets tricky.

Walking down the hierarchy also gets tricky. You seem to have a lot of redundant info. It might be better to have rows that have only

id, manager_id, employee_id, title

However, then you must walk through the "tree" of rows to get to the various levels of management.

However, however, this would let you have a different depth for different departments. (Currently you have a very rigid number of columns.)

"Thousands of records" is not a big deal. "Millions" begins to get interesting. "Billions" would be challenging. (But what company has a billion employees?)

Related Question