Complex Qry, with results in SQL Select

  1. 8 months ago

    Complex Statements, with results in SQL Select :

    INSERT INTO ord_sales_gen (OrdNo, OrdDate, FK_Corp_ID, FK_Valuta)
    SELECT OrdNo+1, '2017-07-16', 7, 1
    FROM ord_sales_gen

    This instruction that works fine in MySql, cannot be used in Xojo Mysql Qry.

    Anyone can help?

  2. Edited 8 months ago

    perform this as 2 separate statements
    and probably put both in a transaction so you can rollback as necessary

  3. I Cannot, if another user does same operation at same time would be having a dangerous result.
    Very remote possibility but exists.

  4. Edited 8 months ago

    Use a transaction
    Thats what they are for

    Issuing this as one "compound" statement also doesn't insure that two users doing the same thing will not collide

  5. I know I looked for a nice compact way, would be fine.
    Many thanks.

  6. transactions are the right way to make sure things get inserted "as expected" and are the right way to handle multi user potential conflicts and errors

    for reading data its not a big deal since there can be many readers at once - but for insert , update, delete they are an absolute MUST to get the right results

or Sign Up to reply!