Search code examples
mysqlsqlinnodbdynamically-generated

MySQL 5.7 GENERATED ALWAYS column defined as subquery

I can't seem to find an issue with my query. I have four tables:

  • agency_info
  • equipment_taken
  • equipment_weight
  • mission_overview

In mission_overview is a column totalWeightInKG, which I want to be calculated by selecting equipment_taken.qty (which is INT), and equipment_weight.equipWeightInKG (which is FLOAT), and multiply them.

So far I have this column definition:

FLOAT GENERATED ALWAYS AS (SELECT qty, equpWeightInKG, (qty*equpWeightInKG) AS totalWeightInKG FROM equipment_taken, equipment_weight) STORED ;

I can't get my head around it... Read the documentation on SELECT queries and joins but still can't seem to come up with a right query...

Solution

  • https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html says:

    Subqueries, parameters, variables, stored functions, and user-defined functions are not permitted.

    To do what you're trying to do, you'll have to write triggers before INSERT and before UPDATE to query the other tables and populate your float column.