Mariadb update column during load data of CSV

Question

I am loading .csv files into Mariadb 10.6 using LOAD DATA LOCAL INFILE.

There is a DATE column called posted_on in the inbound data, and I need store the month name in another field monther, ideally as part of the loading process.

If posted_on = '2023-10-13' then monther = 'October'

I assumed a trigger would be appropriate and have tried variations (added NEW., before/after) on the following:

CREATE TRIGGER `t_add_monther` after INSERT ON `qn_txs` 
 FOR EACH ROW 
 UPDATE qn_txs SET NEW.qn_txs.`monther` = MONTHNAME(`posted_on`);

But can't get past the dreaded ERROR 1442 (HY000) at line 1: Can't update table 'qn_txs' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Any thoughts? Is a different approach needed?

Answer 1

Right, you can't UPDATE/DELETE/INSERT in a trigger for the same table. This could cause an infinite loop of triggers.

But you don't need to use UPDATE if you are changing values in the row being inserted. If you SET NEW.ColumnName in a trigger, that changes the value for that column in the row that spawned the trigger.

CREATE TRIGGER `t_add_monther` BEFORE INSERT ON `qn_txs` 
 FOR EACH ROW 
 SET NEW.`monther` = MONTHNAME(NEW.`posted_on`);

Demo: https://dbfiddle.uk/qC9iCUbg

• br8k: Makes sense. Unfortunately, the CREATE is throwing an error. Looks good to me though. ``` syntax to use near '.monther = MONTHNAME(posted_on)' at line 3 ```
• Bill Karwin: Sorry, I didn't notice that you had done NEW.tableName.columnName. You don't need the table name in that. It is implicit that NEW.columName is naturally based on the table the trigger is defined for. I have edited my answer above to fix that.
• br8k: That did it! I never would have figured that out. PS, your book is in my shopping cart.
• Bill Karwin: Thanks for checking out my book! Make sure you get the revised 2022 edition. Unfortunately, I didn't realize that when I renamed it "SQL Antipatterns Volume 1" that Amazon treats that as a whole new book instead of a second edition of the first book. Here's a direct link to the publisher's page: pragprog.com/titles/bksap1/sql-antipatterns-volume-1
• danblack: Edited into docs thanks @BillKarwin

Answer 2

Don't bother with a TRIGGER, use a temporary variable inside the LOAD DATA:

LOAD DATA
...
(a, b, c, @date, e, f, ...) 
SET date = @date,
 monther = MONTH(@date)
;