I cannot create a table in mysql



  • create table Statistics_tbl with (
    SELECT Todo_tbl.person, SUM(Todo_tbl.duration) FROM Todo_tbl JOIN Statistics_tbl 
    on Todo_tbl.person = Statistics_tbl.person GROUP BY person;
    );
    

    The issue is that the code above does not allow me to create a table Statistics_tbl that would be joined to the other table Todo_tbl.

    The issues reported are:

    • "')' is not valid at this position. Expecting EOF, ALTER, ANALYZE, BEGIN"
    • "'(' is not valid at this position. Expecting an identifier.

    See https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3d5c0e48993306b3ac6db0fe6ecb5a43



  • After I re-reading the answers and comments in https://dba.stackexchange.com/questions/307079/how-can-i-join-two-tables-in-mysql-so-that-whenever-i-add-data-to-table-1-tabl/307085#307085 I think you are looking for a Materialized Query Table (MQT). AFAIK these do not exist in MySQL but you can create your own Incremental Evaluation System (IES) via triggers, see for example: https://fromdual.com/mysql-materialized-views

    I added a small example for an insert trigger at: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=cb04640727e19a430a1df4269051a423 . You will have to add corresponding ones for update and delete. Needless to say, you also need to add keys and not null constraints in your tables so that rows can be safely identified. Disclaimer, I don't know MySQL that well, so I can't tell what concurrency problems (such as lost update) you may have to take into consideration

    What is the problem with the view suggested in the previous answer?




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2