Updating a series with a single query

hi:

I've a series of records, say 1000, have a counter field which is
indexed+no duplicates.
I want to set counter to (counter+1), when I execute this query, it
takes error because of duplicate values, what should I do?

UPDATE Table1 SET id=id+1;

thanks n regards

re: Updating a series with a single query

Abdolhosein Vakilzadeh Ebrahimi wrote:
[color=blue]
> hi:
>
> I've a series of records, say 1000, have a counter field which is
> indexed+no duplicates.
> I want to set counter to (counter+1), when I execute this query, it
> takes error because of duplicate values, what should I do?
>
> UPDATE Table1 SET id=id+1;
>
> thanks n regards[/color]

Convert it to a long int.

re: Updating a series with a single query

This code is untested and if you're using an autonumber field, I'm not sure
if you can update that.
Basically you need to increment your ids starting with the record with the
highest id number.

dim db as database
dim rst as recordset

set db = currentdb()
set rst = db.openrecordset("select * from Table1 order by id desc")

while not rst.eof
rst.edit
rst.fields("id") = rst.fields("id") + 1
rst.update
rst.movenext
wend

rst.close
db.close

"Abdolhosein Vakilzadeh Ebrahimi" <vakilzadeh@kzlabs.com> wrote in message
news:b90d4d8b.0401102224.345bacf0@posting.google.c om...[color=blue]
> hi:
>
> I've a series of records, say 1000, have a counter field which is
> indexed+no duplicates.
> I want to set counter to (counter+1), when I execute this query, it
> takes error because of duplicate values, what should I do?
>
> UPDATE Table1 SET id=id+1;
>
> thanks n regards[/color]

re: Updating a series with a single query

"Abdolhosein Vakilzadeh Ebrahimi" <vakilzadeh@kzlabs.com> wrote in message
news:b90d4d8b.0401102224.345bacf0@posting.google.c om...[color=blue]
> hi:
>
> I've a series of records, say 1000, have a counter field which is
> indexed+no duplicates.
> I want to set counter to (counter+1), when I execute this query, it
> takes error because of duplicate values, what should I do?
>
> UPDATE Table1 SET id=id+1;[/color]

Try first setting the ID to a range that will guarantee no duplicates (add
100000 for example) then run a second query that subtracts 99999 from the ID
value.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

re: Updating a series with a single query

vakilzadeh@kzlabs.com (Abdolhosein Vakilzadeh Ebrahimi) wrote in
news:b90d4d8b.0401102224.345bacf0@posting.google.c om:
[color=blue]
> hi:
>
> I've a series of records, say 1000, have a counter field which
> is indexed+no duplicates.
> I want to set counter to (counter+1), when I execute this
> query, it takes error because of duplicate values, what should
> I do?
>
> UPDATE Table1 SET id=id+1;
>
> thanks n regards[/color]

You are creating a duplicate record 2 when counter is 1, so you
have to renumber 2 before you renumber record 1.

Run the query on the series sorted in descending order.
Then record 2 has already been renumbered to 3 and you dont get the
error.

Bob Q

re: Updating a series with a single query

you need to process the row with the largest ID first,

create a query (qryTable1) that returns the rows in desc ID order
SELECT id FROM table1 ORDER BY id DESC;

then base your update on this query
UPDATE qryTable1 SET id=id+1;


vakilzadeh@kzlabs.com (Abdolhosein Vakilzadeh Ebrahimi) wrote in message news:<b90d4d8b.0401102224.345bacf0@posting.google. com>...[color=blue]
> hi:
>
> I've a series of records, say 1000, have a counter field which is
> indexed+no duplicates.
> I want to set counter to (counter+1), when I execute this query, it
> takes error because of duplicate values, what should I do?
>
> UPDATE Table1 SET id=id+1;
>
> thanks n regards[/color]

re: Updating a series with a single query

HIGH!

THANKS. It worked.