I have a MySQL database with many tables. I follow a tenant based separation, where the every table contains a tenant_id column. Maximum rows per table would be ~billions and average would be in ~300 million. I need to periodically determine the approximate row count for each tenant.

I have the following query:

I have the following query:

CREATE TRIGGER UpdateBalance
AFTER INSERT ON Transactions
FOR EACH ROW
BEGIN
IF NEW.TransactionType = ‘Deposit’ THEN
UPDATE Accounts
SET Balance = Balance + NEW.Amount
WHERE AccountID = NEW.AccountID;
ELSEIF NEW.TransactionType = ‘Withdrawal’ THEN
UPDATE Accounts
SET Balance = Balance – NEW.Amount
WHERE AccountID = NEW.AccountID;
END IF;
END;

How to retrieve earliest ‘date’ for each ‘id’.

Given a row in an example table as:

SELECT loan_applications.province_id AS province_id, loan_applications.branch AS branch, loans.id AS loanid, loans.client_id, loans.loan_application_id, loan_applications.disbursement_date AS disbdate, loan_applications.disbursed_amount AS disbamt, loans.term_period, loan_applications.current_loan_place AS loancycle, loans.last_repayment_date AS maturitydate, clients.dob, JSON_EXTRACT (loan_question_data.input_data, ‘$.gender’) AS gender, JSON_EXTRACT (loan_question_data.input_data, ‘$.martial_status’) AS martial, JSON_EXTRACT (loan_question_data.input_data, ‘$.education’) AS education, loan_repayment_schedule.status, loan_repayment_schedule.remain_balance, loan_repayment_schedule.period, loan_repayment_schedule.repayment_date, loan_repayment_schedule.updated_at FROM loans JOIN loan_applications ON loans.loan_application_id = loan_applications.id JOIN […]

I am trying to compile the following (see below) MySQL stored procedure and I keep getting Error Code 1064.

Using mySQL, I need to update record where there is a null value in a field of type json. For example,

(sale_id, year) is the primary key (combination of columns with unique values) of this table.
product_id is a foreign key (reference column) to Product table.
Each row of this table shows a sale on the product product_id in a certain year.