David Bicking <dbickin-/[EMAIL PROTECTED]> wrote:
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
Something like this:
update requests r set psref =
(select psref from actuals a where r.cust=a.cust and
r.amount=a.amount)
where r.psref is null and
not exists (select * from requests r1 where
r.req_id != r1.req_id and r.cust=r1.cust and r.amount=r1.amount
and r1.psref is null)
and 1 = (select count(*) from actuals a1 where r.cust=a1.cust and
r.amount=a1.amount);
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
Have you tried this query in SQLite? I don't believe it supports this
syntax (a join in the update statement). It does not work for me, but
perhaps I'm not running the latest version.
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?
You can define an ON UPDATE trigger on requests table. This trigger may
invoke a custom function you write.
Igor Tandetnik
