Add calculated column to MySQL table - version 5.6.51

Question

I'm trying to add column 'new_id' to a table following the answers on this post.

The objective is to have new_id = 100*id

My SQL version is 5.6.51

This is the query:

ALTER TABLE `mytable`
ADD `new_id` DOUBLE;

CREATE TRIGGER mytrigger BEFORE INSERT ON `mytable`
FOR EACH ROW
BEGIN
    SET NEW.`new_id` = COALESCE(NEW.`id`*100);

This is indeed generating a 'new_id' column, but all values are NULL and the error generated is:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4

@Akina no, "new_id" is the column I'm trying to create. It is not AUTO INCREMENT — unstuck, Aug 05 '21 at 11:07
Remove the line with the word BEGIN. https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=01728bde63cdbbbebf51625923fd5045 — Akina, Aug 05 '21 at 11:10
It creates the column 'new_id' without errors but all values are NULL — unstuck, Aug 05 '21 at 11:18
If I get it right, the trigger is starting when data is entered into the table. In my table, the column 'id' is already there, I just want it multplied by 100 — unstuck, Aug 05 '21 at 11:22
Edit my fiddle, add your code, reproduce your issue. provide new link. — Akina, Aug 05 '21 at 11:23
Sorry, I don't know how to do that. I just want to multiply a column by 100... — unstuck, Aug 05 '21 at 11:33

score 1 · Accepted Answer · answered Aug 05 '21 at 13:40

After some tries this trigger worked for me:

DELIMITER $$
CREATE TRIGGER MyTrigger BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
    IF NEW.new_id < 0 THEN
        SET NEW.new_id = (select id from your_table order by id desc limit 1) *100;
    ELSEIF NEW.new_id > 0 THEN
        SET NEW.new_id = (select id from your_table order by id desc limit 1) *100;
    ELSEIF NEW.new_id is null THEN
        SET NEW.new_id = (select id from your_table order by id desc limit 1) *100;
    END IF;
END $$
DELIMITER ;

It works in multiple inserted values, too.

score 1 · Answer 2 · answered Aug 05 '21 at 13:43

1

I believe this works:

ALTER TABLE `my_table`
ADD `xxx` DOUBLE;

  
CREATE TRIGGER mytrigger BEFORE UPDATE ON `my_table`
FOR EACH ROW
SET NEW.`new_id` = COALESCE(NEW.`id`*100);

UPDATE `my_table` SET `xxx` =1;

answered Aug 05 '21 at 13:43

unstuck

563
2
12
29

@basha04 you rock! – unstuck Aug 05 '21 at 13:47

Add calculated column to MySQL table - version 5.6.51

2 Answers2