This is more an SQL than SQLITE question.
I have two tables: (The relevant fields are)
CREATE TABLE REQUESTS (
REQ_ID INTEGER PRIMARY KEY,
CUST TEXT,
AMOUNT FLOAT,
PSREF TEXT)
CREATE TABLE ACTUALS (
PSREF TEXT,
CUST TEXT,
AMOUNT FLOAT )
SELECT * FROM REQUESTS;
1 | 100 | 1.01 |
2 | 200 | 2.02 |
3 | 200 | 2.02 |
4 | 300 | 3.03 | 700
5 | 300 | 3.03 |
6 | 400 | 4.04 |
SELECT * FROM ACTUALS;
700 | 300 | 3.03
701 | 100 | 1.01
702 | 200 | 2.02
703 | 300 | 3.03
704 | 400 | 4.04
705 | 400 | 4.04
Now, what I want to do is to update requests with the PSREF value from
actuals, if the request is already set and there is one and only one
unmatched request and one and only one Actual with the same cust and
amount.
SELECT * FROM REQUESTS; -- after update
1 | 100 | 1.01 | 701 can match because there is only one actual
2 | 200 | 2.02 | since there are two 200/2.02 requests, can't tell
3 | 200 | 2.02 | which the actual actually matches.
4 | 300 | 3.03 | 700
5 | 300 | 3.03 | 703 can match since the 700 actual was already matched
6 | 400 | 4.04 | can't match since there are 2 actuals that fit
I figure I need sub-queries, but can't figure out how to state it.
A complication is that the Amount is a float and thus joins on it are
not always accurate.
This is what I have come up with, but it doesn't discount actuals that
have already been matched.
UPDATE REQUESTS R JOIN ACTUALS A
ON R.CUST = A.CUST AND R.AMOUNT = A.AMOUNT
SET R.PSREF = A.PSREF
WHERE R.PSREF IS NULL
AND R.REQ_ID IN (SELECT R.REQ_ID FROM REQUESTS R JOIN ACTUALS A
ON R.CUST = A.CUST AND R.AMOUNT = A.AMOUNT
GROUP BY R.CUST, R.AMOUNT
WHERE R.PSREF IS NULL
HAVING COUNT(*)=1)
Secondly, for each match made, I need to launch an external document (an
excternal file), so I can manually type the PSREF in to that document
and run a macro.
I figure for the launch requirement, I need to run two queries, the
first a select of the matches it could make, which I can step through
and do what I need to do, then the second an update query to actually
update the request table, or can I step through the update query and
"see" what it is updating?
Thanks for any pointers/help you can give,
David
