On Wed, 2006-05-24 at 11:20 -0400, Igor Tandetnik wrote: > David Bicking <dbickin-/[EMAIL PROTECTED]> wrote: > > This is more an SQL than SQLITE question. > >
> 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); > Thanks, I would never have come up with this. Heck, I have to strain to understand exactly what it is doing. > > 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. > No, I hadn't tried it in Sqlite yet. Something in the back of my mind was saying I saw on the list that that doesn't work in Sqlite. I'm more familiar with another dialect of SQL and that does work there. > > 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. > Nice idea. To implement, I would create a function and register it with sqlite, then call the function in the trigger? > Igor Tandetnik > Thanks Igor. David
