 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
swansnow
Guest
|
 Posted: Tue Sep 20, 2005 1:53 pm Post subject: nasty SQL query, please advise... |
 |
|
Three tables: clients, coverage, visits
A client may have one or more coverages. A coverage may have one or
more visits.
Clients pk: clientID
coverage pk: covID, fk:clientID
visits pk: visitID, fk:covID
The query is this:
The visits table needs to store the client number for the client whose
visit this is for (don't ask why...)
I'm not sure how to express the SQL. Here's a beginning:
UPDATE visits
SET clientnum = (SELECT clientID from clients c
LEFT JOIN coverage v on c.clientID = v.clientID
LEFT JOIN visits t on v.covID = t.covID
WHERE .....???? )
I don't know what to put in the WHERE clause.
-Corinna
|
|
| Back to top |
|
 |
Maarten Wiltink
Guest
|
| Posted: Tue Sep 20, 2005 2:57 pm Post subject: Re: nasty SQL query, please advise... |
|
|
"swansnow" <schultz (AT) harlingen (DOT) isd.tenet.edu> wrote
| Quote: | Three tables: clients, coverage, visits
A client may have one or more coverages. A coverage may have one or
more visits.
Clients pk: clientID
coverage pk: covID, fk:clientID
visits pk: visitID, fk:covID
The query is this:
The visits table needs to store the client number for the client whose
visit this is for (don't ask why...)
I'm not sure how to express the SQL. Here's a beginning:
UPDATE visits
SET clientnum = (SELECT clientID from clients c
LEFT JOIN coverage v on c.clientID = v.clientID
LEFT JOIN visits t on v.covID = t.covID
WHERE .....???? )
I don't know what to put in the WHERE clause.
|
(visits.visitID = t.visitID). I think. You may need an alias on the
Visits table as referenced by the UPDATE query itself, to disambiguate
it from t.
What you want is to update only the record from where you started
joining.
Can't you UPDATE a JOIN directly? That way, you wouldn't need a WHERE
clause at all. I think.
Groetjes,
Maarten Wiltink
|
|
| Back to top |
|
|
swansnow
Guest
|
| Posted: Tue Sep 20, 2005 3:32 pm Post subject: Re: nasty SQL query, please advise... |
|
|
unfortunately, I don't think I can do this in a single query. It turns
out there is no visitID -- I made an assumption. At any rate, I didn't
know you were allowed to refer to the outer table from inside a
subquery.
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...
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!
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.
Thanks anyway...
-Corinna
|
|
| Back to top |
|
|
Maarten Wiltink
Guest
|
| Posted: Tue Sep 20, 2005 8:11 pm Post subject: Re: nasty SQL query, please advise... |
|
|
"swansnow" <schultz (AT) harlingen (DOT) isd.tenet.edu> wrote
| Quote: | 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.
| Quote: | 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.
| Quote: | 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?
| Quote: | 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.
| Quote: | 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.
| Quote: | 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
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
