0

Apologies I'm brand new to SQL. I'm trying to add a column to SQL that calculates the number of days difference between as shipping date and todays date.

The following works perfectly when I want to view the days

SELECT DATEDIFF(now(),shipping_date) from tracking as days_transit

But when I try to make a new column with the following code I get errors

alter table tracking add days_transit as DATEDIFF(now(),shipping_date)

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as cast(DATEDIFF(now(),shipping_date))' at line 1

What am I doing wrong?! I am using phpmyadmin

inetphantom
  • 2,498
  • 4
  • 38
  • 61
allanf
  • 37
  • 5
  • [Edit] to identify your MySQL version, e.g. this wouldn't work prior to 5.7: https://stackoverflow.com/questions/37789138/computed-column-in-mysql – underscore_d Apr 21 '21 at 14:36
  • Do you need a calculated/computed column? Or can you just use a view that adds the column you want? – underscore_d Apr 21 '21 at 14:47
  • Does this answer your question? [Computed Column in Mysql](https://stackoverflow.com/questions/37789138/computed-column-in-mysql) – Nico Haase Apr 21 '21 at 14:56
  • Or this? https://stackoverflow.com/questions/5222044/column-calculated-from-another-column – Nico Haase Apr 21 '21 at 14:56

1 Answers1

-1

Just alter table to add the new column first with no values. Then next update the column values accordingly with update command.

underscore_d
  • 6,309
  • 3
  • 38
  • 64
Nikhil Gopal Sonurlekar
  • 9
  • 2
  • It seems like they wanted a calculated/computed column, though, i.e. one that will be calculated from its source expressions whenever queried, not one that is set once and left, or one that requires update, to be out of date. – underscore_d Apr 21 '21 at 14:47
  • How should this work later, when a new column is added? – Nico Haase Apr 21 '21 at 14:55
  • Hi - Thanks - your suggestion worked fine and it calculated all the data for items already in the table, but it appears to leave them blank for all new additions. The above poster is right. I need this calculated continually every time a new row is added. – allanf Apr 21 '21 at 15:09