MySQL trigger to update a field to the value of id

by

I don’t know of any way to do this in one statement, even using a trigger.

The trigger solution that @Lucky suggested would look like this in MySQL:

CREATE TRIGGER MyTrigger BEFORE INSERT ON MyTable
FOR EACH ROW BEGIN
  SET NEW.group_id = COALESCE(NEW.group_id, NEW.id);
END

However, there’s a problem. In the BEFORE INSERT phase, the auto-generated id value hasn’t been generated yet. So if group_id is null, it defaults to NEW.id which is always 0.

But if you change this trigger to fire during the AFTER INSERT phase, so you have access to the generated value of NEW.id, you can’t modify column values.

MySQL doesn’t support expressions for the DEFAULT of a column, so you can’t declare this behavior in the table definition either. *Update: MySQL 8.0.13 supports DEFAULT (<expression>) but the expression still can’t depend on an auto-increment value (this is documented).

The only solution is to do the INSERT, and then immediately do an UPDATE to change the group_id if it’s not set.

INSERT INTO MyTable (group_id, value) VALUES (NULL, 'a');
UPDATE MyTable SET group_id = COALESCE(group_id, id) WHERE id = LAST_INSERT_ID();

More Related Contents:

  1. MySQL trigger On Insert/Update events
  2. “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”
  3. How to set initial value and auto increment in MySQL?
  4. How to do a batch insert in MySQL
  5. LAST_INSERT_ID() MySQL
  6. Increment a database field by 1
  7. How to program a MySQL trigger to insert row into another table?
  8. TRIGGERs that cause INSERTs to fail? Possible?
  9. How to copy a row and insert in same table with a autoincrement field in MySQL?
  10. MySQL Error 1264: out of range value for column
  11. BULK INSERT in MYSQL
  12. Importing a CSV to MySQL with different date format
  13. mysql trigger stored trigger is already used by statement which invoked stored trigger
  14. How to insert values in table with foreign key using MySQL?
  15. Field ‘id’ doesn’t have a default value?
  16. MySQL INSERT INTO … VALUES and SELECT
  17. MySQL error 1241: Operand should contain 1 column(s)
  18. insert data from one table to another in mysql
  19. What are practical differences between `REPLACE` and `INSERT … ON DUPLICATE KEY UPDATE` in MySQL?
  20. Mysql insert into 2 tables
  21. Can I launch a trigger on select statement in mysql?
  22. Using MySQL triggers to log all table changes to a secondary table
  23. MySQL optimizing INSERT speed being slowed down because of indices
  24. MySQL: Insert datetime into other datetime field
  25. MySQL – How to parse a string value to DATETIME format inside an INSERT statement?
  26. Incorrect integer value: ” for column ‘id’ at row 1
  27. MySQL listen notify equivalent
  28. MySQL disable all triggers
  29. Calling an url from a trigger in mysql
  30. Is there a way to use ON DUPLICATE KEY to Update all that I wanted to insert?

Leave a Comment