Re: how to combine update en select in vb6 SQL
- From: Catharinus <csvanderwerf@xxxxxxxxx>
- Date: Sat, 29 Jan 2011 07:51:47 -0800 (PST)
On 29 jan, 15:05, Catharinus <csvanderw...@xxxxxxxxx> wrote:
On 29 jan, 01:15, "MikeD" <nob...@xxxxxxxxxxx> wrote:
"Catharinus" <csvanderw...@xxxxxxxxx> wrote in message
news:cf0d7191-91ea-4e81-aef5-7b484af31e1d@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hello my friends
I have a query that updates a column in TABELR.
The query collects selected data from TABELB where TABELB.Reken=
TABELR.Reken.
Because I do'n't know how to combine an update statement in a select
statement, I simply walk throuth the recorsset by moving from record
to the follwowing record:
Set mrsttest4 = conConnection.Execute("Select * from TABELR order by
reken")
do while mrst4.Eof = false
figure=conconnection.executte("Select bedrag from TABELB where....")
conconnection.execute(update TABELB.Bedrag = "& figure)
etc.
loop
But this is very slow, in fact too slow.
Not really a VB issue from what I can see. From what I can understand of
your question, this is strictly a SQL issue. And it sounds to me like you
need to use a JOIN.
UPDATE TABELR SET
TABELR.Bedrag = TABLEB.Bedrag
FROM TABLER
INNER JOIN TABLEB ON TABELR.Reken = TABLEB.Reken
WHERE <perhaps what you cut off in your code snippet>
Also, please copy and paste the code snippets you're including that are
presumably intended to help explain/clarify things. There are so many typos
and other coding errors in what you posted that we really can't be sure
we're getting the whole picture. For example, I'm assuming that the
variable mrst4 is really supposed to be mrsttest4 (or vice-versa)?
--
Mike- Tekst uit oorspronkelijk bericht niet weergeven -
- Tekst uit oorspronkelijk bericht weergeven -
Thanks to you all, I will try to speed things up this way..
Thanks
Catharinus- Tekst uit oorspronkelijk bericht niet weergeven -
- Tekst uit oorspronkelijk bericht weergeven -
I have discoverd that using a connection in a do while loop causes the
greatest delay.
So I use a recordset instead of a connection.
Hope that this will solve the delay-problem.
Thanks again
Catharinus
.
- Follow-Ups:
- Re: how to combine update en select in vb6 SQL
- From: Catharinus
- Re: how to combine update en select in vb6 SQL
- References:
- how to combine update en select in vb6 SQL
- From: Catharinus
- Re: how to combine update en select in vb6 SQL
- From: MikeD
- Re: how to combine update en select in vb6 SQL
- From: Catharinus
- how to combine update en select in vb6 SQL
- Prev by Date: Re: Branch Lines
- Next by Date: problem with active reports after installation
- Previous by thread: Re: how to combine update en select in vb6 SQL
- Next by thread: Re: how to combine update en select in vb6 SQL
- Index(es):
Relevant Pages
|
