update all rows after insertion in mysql I am working with mysql and I have a table with the following structure (a summary):

CREATE TABLE `costs` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`utility` DECIMAL(9,2) UNSIGNED NOT NULL,
`tax` DECIMAL(9,2) UNSIGNED NOT NULL,
`active` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
`created_at` TIMESTAMP NULL DEFAULT NULL,
`updated_at` TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (`id`),
)


where the active field defaults to 1 when inserting, then i would like when saving a new record all other rows update the active field as 0, so i try to create a trigger for this but i am getting a mysql error.

DELIMITER //
CREATE TRIGGER after_costs_insert AFTER INSERT ON costs FOR EACH ROW 
BEGIN
  UPDATE costs SET active = 0 WHERE id &lt;&gt; NEW.id;
END;
//
DELIMITER ;


I think it is not possible to do this, so how can I update these rows?
Updating all rows in a table is seriously non-scalable.

Consider tossing active and updated_at.  Change created_at to be DEFAULT CURRENT_TIMESTAMP (or something like that).  And have INDEX(created_at).  Then,

SELECT ...
    ORDER BY created_at DESC  LIMIT 1


Will get the "latest" row (which seems to be your "active").

If there is a risk of inserting two rows in the same second, there are workarounds.