When doing raw SQL updates do not forget timestamps

This is part of the Semicolon&Sons Code Diary - consisting of lessons learned on the job. You're in the databases category.

Last Updated: 2022-12-01

In raw SQL - instead of through my ORM - I updated all rows in the advisors table that had empty company_name columns to have their company_name set to their first and last name. Later on, this turned out to be a bad idea and I wished to revert it. I was hoping to identify the records by their updated_at field but this was not possible because the SQL update I wrote did not set the updated_at field, instead leaving it at its original value. This removed my ability to easily undo my mistake.

There could have been two ways to deal with this.

First, to set to NOW() in the SQL for modifying the table

UPDATE advisors 
   SET updated_at = NOW(), company_name = "..."
 WHERE company_name IS NULL;

Second, to use a trigger


When doing a big change in the production database as opposed to through an ORM, don't forget to bump the updated_at field in raw SQL. Otherwise it is more dificult to identify those changes.