-1

I'd like to have a column that has a value dependant on other columns of the same table in MySQL Server 5.7 (InnoDB Cluster).

As a base for my statement, and as a test, I've used the following instruction:

ALTER TABLE contracts
ADD COLUMN curr_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

But I want my column to be a DATE and to have a value that is always a date resulting from the formula ADDDATE(order_loaded_on, INTERVAL FLOOR(delivery_time * 1.4) DAY) (where order_loaded_on is a DATE and delivery_time is an INT), so I wrote and ran the following:

ALTER TABLE contracts
ADD COLUMN est_delivery_on DATE
DEFAULT ADDDATE(order_loaded_on, INTERVAL FLOOR(delivery_time * 1.4) DAY)
ON UPDATE ADDDATE(order_loaded_on, INTERVAL FLOOR(delivery_time * 1.4) DAY)

But it returns a syntax error near to ADDDATE(order_loaded_on, INTERVAL FLOOR(delivery_time * 1.4) DAY) on line 4, so I ran it without the ON UPDATE clause and it works. The problem is that without the latter clause it doesn't update when the record changes.

Where am I doing wrong?

UPDATE:

As per suggestion, I've tried to add it as a generated column running the following:

ALTER TABLE CrmContratto ADD COLUMN PrevistaConsegna DATE AS ADDDATE(DataCaricamentoOrdine, INTERVAL FLOOR(TempiConsegna * 1.4) DAY)

But it returns another syntax error.

The correct statement is in the accepted answer. Thanks.

  • I think a calculated column will be better than a default here. Look at this other post for reference: https://stackoverflow.com/q/5222044/2557263 – Alejandro Aug 30 '21 at 15:45
  • @Alejandro Thanks for your comment. I didn't think about calculated columns and I agree that it would be better. I've tried to run `ALTER TABLE CrmContratto ADD COLUMN PrevistaConsegna DATE AS ADDDATE(DataCaricamentoOrdine, INTERVAL FLOOR(TempiConsegna * 1.4) DAY)` but it returns another syntax error. –  Aug 30 '21 at 15:56
  • Update the question with this new attempt and the exact error message you've got. Also include the exact server version you're using. – Alejandro Aug 30 '21 at 16:07
  • The column of DATE datatype may have only literal, not expressional, DEFAULT and ON UPDATE values. – Akina Aug 30 '21 at 16:39

1 Answers1

1

You can't use a function in ON UPDATE.

But you can use a generated column, which does the same as it gets generates its values when you insert or update the row

ALTER TABLE contracts
ADD COLUMN est_delivery_on DATE
GENERATED ALWAYS AS (order_loaded_on + INTERVAL FLOOR(delivery_time * 1.4) DAY) STORED
Alejandro
  • 7,290
  • 4
  • 34
  • 59
nbk
  • 45,398
  • 8
  • 30
  • 47