"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:452464D8-61CF-44E6-8CEE-D14E55E5BD13@news.elevatesoft.com...
> Dieter,
>
> << Hello Tim, yes DBISAM V4. >>
>
> Could you send me the tables that you're using along with the SQL ?  I'm
> obviously missing something here, because it looks fine to me.
>

It is not fine, I'm afraid. This was the original SQL

select runde into Test from probe order by runde;
optimize table Test;
alter table Test last autoinc  0;
alter table Test add myautoinc autoinc;


at this point, test contains runde and myautoinc. it is in order by runde,
and myautoinc is in sequence. So far so good

This is the SQL he's running now

update probe set runde = myautoinc from probe
join test on probe.runde = test.myautoinc;

He's joining the tables ON a field that is being updated. Strange, right?

What he needs to do is select a unique field on the select into test, and
then join the two tables on that field.

Robert

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote:


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:452464D8-61CF-44E6-8CEE-D14E55E5BD13@news.elevatesoft.com...
> Dieter,
>
> << Hello Tim, yes DBISAM V4. >>
>
> Could you send me the tables that you're using along with the SQL ?  I'm
> obviously missing something here, because it looks fine to me.
>

It is not fine, I'm afraid. This was the original SQL

select runde into Test from probe order by runde;
optimize table Test;
alter table Test last autoinc  0;
alter table Test add myautoinc autoinc;


at this point, test contains runde and myautoinc. it is in order by runde,
and myautoinc is in sequence. So far so good

This is the SQL he's running now

update probe set runde = myautoinc from probe
join test on probe.runde = test.myautoinc;

He's joining the tables ON a field that is being updated. Strange, right?

What he needs to do is select a unique field on the select into test, and
then join the two tables on that field.

Robert


Hello Robert,
I will change the content runde to the content of myautoinc.
Sorry that I was not clear.

TIA
Dieter

"Dieter Nagy" <dieter.nagy@tele2.at> wrote in message
news:CF2E9DCF-2067-497E-A509-6830B25F5364@news.elevatesoft.com...
> "Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote:
>
>
> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
> news:452464D8-61CF-44E6-8CEE-D14E55E5BD13@news.elevatesoft.com...
>> Dieter,
>>
>> << Hello Tim, yes DBISAM V4. >>
>>
>> Could you send me the tables that you're using along with the SQL ?  I'm
>> obviously missing something here, because it looks fine to me.
>>
>
> It is not fine, I'm afraid. This was the original SQL
>
> select runde into Test from probe order by runde;
> optimize table Test;
> alter table Test last autoinc  0;
> alter table Test add myautoinc autoinc;
>
>
> at this point, test contains runde and myautoinc. it is in order by runde,
> and myautoinc is in sequence. So far so good
>
> This is the SQL he's running now
>
> update probe set runde = myautoinc from probe
> join test on probe.runde = test.myautoinc;
>
> He's joining the tables ON a field that is being updated. Strange, right?
>
> What he needs to do is select a unique field on the select into test, and
> then join the two tables on that field.
>
> Robert
>
>
> Hello Robert,
> I will change the content runde to the content of myautoinc.
> Sorry that I was not clear.
>

It was clear. You are changing the field that you are ALSO using to link the
two tables. In addition, you are joining the two tables ON a field
(myautinc) that does not exist in the original table.

I believe you need to think this thing thru one more time. If your table has
a UNIQUE field (lt's call it ID) then this is the SQL

Table
Runde ID Field
7         2   John
4         5  Jim
1         4  Mary

SELECT Runde, ID into Test from table order by runde;
optimize table Test;
alter table Test add myautoinc autoinc;

Now test contains
Runde ID MyAutoinc
1         4   1
4         5    2
7         2    3

then

update table set runde = myautoinc from table
join test on table.ID = test.ID;

Robert

Robert,

<< update probe set runde = myautoinc from probe
join test on probe.runde = test.myautoinc;

He's joining the tables ON a field that is being updated. Strange, right?
>>

Yes, and I completely missed that.  Dieter ?  What is the purpose of trying
to update the column that you're also using to join to another table ?

<< What he needs to do is select a unique field on the select into test, and
then join the two tables on that field. >>

That will probably be the only way to do it so as to not disturb the joins
in the process of executing the updates.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:03B0CC84-BD26-4971-BB6A-0921212CD766@news.elevatesoft.com...
> Robert,
>
> << update probe set runde = myautoinc from probe
> join test on probe.runde = test.myautoinc;
>
> He's joining the tables ON a field that is being updated. Strange, right?
> >>
>
> Yes, and I completely missed that.  Dieter ?  What is the purpose of
> trying to update the column that you're also using to join to another
> table ?
>
> << What he needs to do is select a unique field on the select into test,
> and then join the two tables on that field. >>
>
> That will probably be the only way to do it so as to not disturb the joins
> in the process of executing the updates.
>

I think this needs to be slightly redesigned If the purpose is to end
up with a runde of sequential numbers, there is no need to create another
table. Just optimize on runde, add the autoinc field, drop runde and rename
the autoinc to runde. Voila.

Robert

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote:


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:03B0CC84-BD26-4971-BB6A-0921212CD766@news.elevatesoft.com...
> Robert,
>
> << update probe set runde = myautoinc from probe
> join test on probe.runde = test.myautoinc;
>
> He's joining the tables ON a field that is being updated. Strange, right?
> >>
>
> Yes, and I completely missed that.  Dieter ?  What is the purpose of
> trying to update the column that you're also using to join to another
> table ?
>
> << What he needs to do is select a unique field on the select into test,
> and then join the two tables on that field. >>
>
> That will probably be the only way to do it so as to not disturb the joins
> in the process of executing the updates.
>

I think this needs to be slightly redesigned If the purpose is to end
up with a runde of sequential numbers, there is no need to create another
table. Just optimize on runde, add the autoinc field, drop runde and rename
the autoinc to runde. Voila.

Robert


Robert, many thanks for your help!

Dieter

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Robert,

<< update probe set runde = myautoinc from probe
join test on probe.runde = test.myautoinc;

He's joining the tables ON a field that is being updated. Strange, right?
>>

Yes, and I completely missed that.  Dieter ?  What is the purpose of trying
to update the column that you're also using to join to another table ?

<< What he needs to do is select a unique field on the select into test, and
then join the two tables on that field. >>

That will probably be the only way to do it so as to not disturb the joins
in the process of executing the updates.

--
Tim Young
Elevate Software
www.elevatesoft.com


Tim, many thanks for your help!

Dieter