Re: nasty SQL query, please advise...

"swansnow" <schultz@xxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1127230342.740740.215200@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

> unfortunately, I don't think I can do this in a single query.

I think you can. My guess is that the following is correct SQL:

UPDATE visits
INNER JOIN coverage
ON (visits.covid = coverage.covid)
SET visits.clientnum = coverage.clientid;

It is a guess; it may not work like that. But it seems like just
the sort of thing that _does_ work in SQL.

The JOIN is INNER rather than LEFT; if there is no link you don't
want to update the visits record anyway. You may or may not need a
check that the clientid is not null.


> It turns out there is no visitID -- I made an assumption.

Ouch. Nasty. Having a uniqueid in all databearing tables was
mentioned, if not exactly taught, as a good habit back when I
was in university. Making it quite some time ago already. By
now, I believe it's firmly established as a best practice.


> At any rate, I didn't
> know you were allowed to refer to the outer table from inside a
> subquery.

Off the bat I'd have to wonder what use they are without that?


> I don't have a lot of experience with the more complex queries. My
> SQL reference book doesn't have anything about using an update and
> a join in the same statement...

I don't have an SQL reference book; if I need one I look up the T-SQL
reference on MSDN.


> Also, the query can be simplified (and made faster) because I don't
> need to join with the clients table -- the clientnumber is in the
> coverage table!

Yes, of course. A common trick. Fortunately, it's always clearly
visible in your query when it applies.


> I decided to write a little program that will iterate through the
> visits table, and look up the client number for each record in the
> visits table. It will be slower than a query, though, I think, and
> this is a very large table... However, I won't need to do any joins,
> and the index will be used, so maybe the speed is comparable.

It may well be *very* *much* slower than a query. All the books seem to
agree on this: if at all possible, work on recordsets, not individual
records. Leave loops within the server.

Groetjes,
Maarten Wiltink


.

Relevant Pages

  • Re: query error
    ... If you're not familiar with SQL, open the query in Design view, then select ... relationship is between ClientID in Client and customer in company. ... "John W. Vinson" wrote: ...
    (microsoft.public.access.queries)
  • Re: Help With Multiple Tables, Fields and query -
    ... Create a query that selects the data and shows the fields you want to update as ... Switch to SQL view ... Desire to dump the ~ *and* to separate the first and last names INTO ... FirstName, LastName, Organization Add ClientID and the ...
    (microsoft.public.access.gettingstarted)
  • Re: Help With Multiple Tables, Fields and query -
    ... Create a query that selects the data and shows the fields you want to update as ... Switch to SQL view ... Desire to dump the ~ *and* to separate the first and last names INTO ... FirstName, LastName, Organization Add ClientID and the ...
    (microsoft.public.access.gettingstarted)
  • Re: Autonumber using alphanumerics
    ... entered the calculation you suggested and the problem remains when I switch ... The ClientID Number is an autonumber and listed as such in design view, ... The 2 tables that I have used to create the query are Corporate Client ... Below is the SQL for the query. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)