I added four new columns to a DailyTotals table that already has many rows and I need to populate the new columns with daily totals for each row by querying all daily pawn transactions.
The DailyTotals table looks like this:
tblDailyTotals
(
dt_date DATE, {unique index}
...
dt_P_count INT,
dt_P_amt DECIMAL(8,2),
dt_X_count INT,
dt_X_amt DECIMAL(8,2)
);
The Pawn Transactions table looks like this:
tblPawnTransactions
(
...
pt_date DATE, {dups index}
pt_type CHAR(1), {"P","X"}
pt_amt DECIMAL(7,2)
);
How would you code the mass update query to populate the four new columns for each daily row in the DailyTotals table?
Here's some working Informix SQL. It assumes you have DBDATE='Y4MD-' or equivalent set in the environment — you'll need to adjust the date formats to suit your preferences.
CREATE TABLE tblDailyTotals
(
dt_date DATE PRIMARY KEY NOT NULL, {unique index}
dt_other CHAR(20) NOT NULL,
dt_P_count INT,
dt_P_amt DECIMAL(8,2),
dt_X_count INT,
dt_X_amt DECIMAL(8,2)
);
CREATE TABLE tblPawnTransactions
(
pt_customer INT,
pt_date DATE, {dups index}
pt_type CHAR(1), {"P","X"}
pt_amt DECIMAL(7,2)
);
INSERT INTO tblDailyTotals(dt_date, dt_other) VALUES('2017-01-01', 'Other info 2017-01-01');
INSERT INTO tblDailyTotals(dt_date, dt_other) VALUES('2017-01-02', 'Other info 2017-01-02');
INSERT INTO tblDailyTotals(dt_date, dt_other) VALUES('2017-01-03', 'Other info 2017-01-03');
INSERT INTO tblDailyTotals(dt_date, dt_other) VALUES('2017-01-04', 'Other info 2017-01-04');
INSERT INTO tblDailyTotals(dt_date, dt_other) VALUES('2017-01-05', 'Other info 2017-01-05');
INSERT INTO tblDailyTotals(dt_date, dt_other) VALUES('2017-01-06', 'Other info 2017-01-06');
UPDATE tblDailyTotals
SET dt_P_count = 3, dt_P_amt = 345.67, dt_X_count = 9, dt_X_amt = 291.23
WHERE dt_date = '2017-01-06';
INSERT INTO tblPawnTransactions VALUES(1, '2017-01-01', 'P', 12.34);
INSERT INTO tblPawnTransactions VALUES(2, '2017-01-01', 'P', 22.25);
INSERT INTO tblPawnTransactions VALUES(3, '2017-01-01', 'X', 32.16);
INSERT INTO tblPawnTransactions VALUES(1, '2017-01-02', 'P', 42.07);
INSERT INTO tblPawnTransactions VALUES(4, '2017-01-02', 'P', 52.98);
INSERT INTO tblPawnTransactions VALUES(2, '2017-01-02', 'X', 62.89);
INSERT INTO tblPawnTransactions VALUES(1, '2017-01-03', 'P', 72.70);
INSERT INTO tblPawnTransactions VALUES(1, '2017-01-03', 'X', 82.51);
INSERT INTO tblPawnTransactions VALUES(1, '2017-01-03', 'P', 92.42);
INSERT INTO tblPawnTransactions VALUES(2, '2017-01-05', 'X', 13.33);
INSERT INTO tblPawnTransactions VALUES(1, '2017-01-05', 'P', 14.14);
INSERT INTO tblPawnTransactions VALUES(1, '2017-01-05', 'X', 15.05);
SELECT * FROM tblDailyTotals ORDER BY dt_date;
UPDATE tblDailyTotals
SET (dt_P_count, dt_P_amt) = ((SELECT COUNT(*), SUM(pt_amt)
FROM tblPawnTransactions AS pt
WHERE pt.pt_date = tblDailyTotals.dt_date
AND pt.pt_type = 'P'
))
WHERE EXISTS(SELECT * FROM tblPawnTransactions AS pt
WHERE pt.pt_date = tblDailyTotals.dt_date AND pt_type = 'P')
;
UPDATE tblDailyTotals
SET (dt_X_count, dt_X_amt) = ((SELECT COUNT(*), SUM(pt_amt)
FROM tblPawnTransactions AS pt
WHERE pt.pt_date = tblDailyTotals.dt_date
AND pt.pt_type = 'X'
))
WHERE EXISTS(SELECT * FROM tblPawnTransactions AS pt
WHERE pt.pt_date = tblDailyTotals.dt_date AND pt_type = 'X')
;
The double parentheses are necessary; the outer pair indicates that there is a list of values on the RHS of the SET clause, to match the list of columns in parentheses on the LHS of the SET clause, while the inner parentheses indicate that the values are the result of a SELECT statement.
Before update:
2017-01-01 Other info 2017-01-0
2017-01-02 Other info 2017-01-0
2017-01-03 Other info 2017-01-0
2017-01-04 Other info 2017-01-0
2017-01-05 Other info 2017-01-0
2017-01-06 Other info 2017-01-0 3 345.67 9 291.23
After update:
2017-01-01 Other info 2017-01-0 2 34.59 1 32.16
2017-01-02 Other info 2017-01-0 2 95.05 1 62.89
2017-01-03 Other info 2017-01-0 2 165.12 1 82.51
2017-01-04 Other info 2017-01-0
2017-01-05 Other info 2017-01-0 1 14.14 2 28.38
2017-01-06 Other info 2017-01-0 3 345.67 9 291.23
What happens if you omit the WHERE EXISTS(…) clauses? Rows which don't have matching entries in the tblPawnTransactions table get updated too, like this:
SELECT * FROM tblDailyTotals ORDER BY dt_date;
UPDATE tblDailyTotals
SET (dt_P_count, dt_P_amt) = ((SELECT COUNT(*), SUM(pt_amt)
FROM tblPawnTransactions AS pt
WHERE pt.pt_date = tblDailyTotals.dt_date
AND pt.pt_type = 'P'
))
;
UPDATE tblDailyTotals
SET (dt_X_count, dt_X_amt) = ((SELECT COUNT(*), SUM(pt_amt)
FROM tblPawnTransactions AS pt
WHERE pt.pt_date = tblDailyTotals.dt_date
AND pt.pt_type = 'X'
))
;
SELECT * FROM tblDailyTotals ORDER BY dt_date;
This generates:
2017-01-01 Other info 2017-01-0 2 34.59 1 32.16
2017-01-02 Other info 2017-01-0 2 95.05 1 62.89
2017-01-03 Other info 2017-01-0 2 165.12 1 82.51
2017-01-04 Other info 2017-01-0 0 0
2017-01-05 Other info 2017-01-0 1 14.14 2 28.38
2017-01-06 Other info 2017-01-0 0 0
Notice that the rows for 2017-01-04 and 2017-01-06 both got updated. Now, if you want those zeroes and nulls in place, that's fine. If not, use the EXISTS clause.