I need to do an insert into a table of each shipment that day. I am having a problem deducting the total amount from the previous row. I am inserting into tableC, but since if I have multiple orders in a day its only grabbing the last debit entry and deducting from the original last rows total. I know this is because I am using the ORDER BY DESC LIMIT 1, but I can't think of another way to get the last rows total column value. I also need the second insertion to get the new total from the previous insertion.
I am using a select statement inside a variable to run inside my insert statment. @vTotal
If its a single entry insertion it would work, but how do I this to work if there is multiple rows in the select statement, or is there a better way to do this?
Below is my current test query:
SET @vCreatedbt := '[email protected]';
SET @vTotal := (SELECT tableC.total - tableB.ShipmentCharge AS total
FROM tableC, tableB
ORDER BY id DESC
LIMIT 1);
SET @vwork_order_id := null;
SET @vModified := null;
SET @vModified_by := null;
SET @vCredit := null;
SET @vid := null;
INSERT INTO tableC
SELECT @vId AS id, client_name AS descr, @vWork_order_id AS work_order_id,
@vCredit AS credit, ShipmentCharge as debit, @vTotal AS total,
now() as created, @vCreatedby as createdby,
@vModified AS modified, @vModified_by AS modified_by
FROM tblA
INNER JOIN tableD ON tableA.id = tableD.id
INNER JOIN tableB ON tableD.tracking_number = tableB.TrackingNumber
WHERE tableA.status = 'Order Shipped'
Just showing these three fields of tableC to simplify
| id | debit | total |
|---|---|---|
| 1 | 27.37 | 15260.96 |
Query is giving me
| id | debit | total |
|---|---|---|
| 1 | 27.37 | 15260.96 |
| 2 | 25.96 | 15235.96 |
| 3 | 25.00 | 15235.96 |
The id is auto incremented
CREATE TABLE balance (
id INT AUTO_INCREMENT PRIMARY KEY,
person_id INT NOT NULL,
action DECIMAL(10, 2) NOT NULL,
balance DECIMAL(10, 2) NOT NULL DEFAULT 0,
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
CONSTRAINT balance_cannot_be_negative CHECK (balance >= 0)
);
CREATE TRIGGER set_balance
BEFORE INSERT ON balance
FOR EACH ROW
SET NEW.balance = COALESCE(
(
SELECT balance
FROM balance
WHERE person_id = NEW.person_id
ORDER BY created_at DESC LIMIT 1
), 0) + NEW.action;
INSERT INTO balance (person_id, action) VALUES (1, 100); -- add $100, balance $100
INSERT INTO balance (person_id, action) VALUES (1, -20); -- spend $20, balance $80
INSERT INTO balance (person_id, action) VALUES (2, 50); -- add $50, balance $50
INSERT INTO balance (person_id, action) VALUES (1, 10); -- add $10, balance $90
INSERT INTO balance (person_id, action) VALUES (2, 30); -- add $30, balance $80
INSERT INTO balance (person_id, action) VALUES (2, -40); -- spend $40, balance $40
-- try to spend $100 which is over current balance, error generated
INSERT INTO balance (person_id, action) VALUES (2, -100);
Check constraint 'balance_cannot_be_negative' is violated.
SELECT * FROM balance ORDER BY id;
SELECT * FROM balance ORDER BY person_id, created_at;
| id | person_id | action | balance | created_at |
|---|---|---|---|---|
| 1 | 1 | 100.00 | 100.00 | 2023-06-08 18:05:59.811375 |
| 2 | 1 | -20.00 | 80.00 | 2023-06-08 18:05:59.817546 |
| 3 | 2 | 50.00 | 50.00 | 2023-06-08 18:05:59.819130 |
| 4 | 1 | 10.00 | 90.00 | 2023-06-08 18:05:59.820016 |
| 5 | 2 | 30.00 | 80.00 | 2023-06-08 18:05:59.821140 |
| 6 | 2 | -40.00 | 40.00 | 2023-06-08 18:05:59.822273 |
| id | person_id | action | balance | created_at |
|---|---|---|---|---|
| 1 | 1 | 100.00 | 100.00 | 2023-06-08 18:05:59.811375 |
| 2 | 1 | -20.00 | 80.00 | 2023-06-08 18:05:59.817546 |
| 4 | 1 | 10.00 | 90.00 | 2023-06-08 18:05:59.820016 |
| 3 | 2 | 50.00 | 50.00 | 2023-06-08 18:05:59.819130 |
| 5 | 2 | 30.00 | 80.00 | 2023-06-08 18:05:59.821140 |
| 6 | 2 | -40.00 | 40.00 | 2023-06-08 18:05:59.822273 |
DATETIME(6) needed in the fiddle - common DATETIME does not provide needed accuracy. In practice DATETIME must be enough - I doubt that the person can perform two separate transactoins within a second.. or you may use not created_at but id for rows ordering.