Hi Ng,

In my database, I have a column, containing identifiers (numbers), which I
would like to keep unique. Some times, it is necessary to increment some (or
all) of the number by one.

Just doing

update table set uniquecol=uniquecol +1

generates an error.

How can I keep the unique index on my column, but stil be able to increment
the values, without unnecessary overhead.?

--
/Kasper

On Jan 7, 4:15 pm, "Kasper Lindberg" <N...@m.invalid> wrote:
> Hi Ng,
>
> In my database, I have a column, containing identifiers (numbers), which I
> would like to keep unique. Some times, it is necessary to increment some (or
> all) of the number by one.
>
> Just doing
>
> update table set uniquecol=uniquecol +1
>
> generates an error.
>
> How can I keep the unique index on my column, but stil be able to increment
> the values, without unnecessary overhead.?
>
> --
> /Kasper

Is there a reason you're not using the autoincrement option that MySQL
has?

It would be something like...

DROP TABLE IF EXISTS `tableName`;
CREATE TABLE `tableName` (
`uid` smallint(6) NOT NULL auto_increment,
`name` varchar(50) NOT NULL default ''
PRIMARY KEY (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

HTH
D.

Kasper Lindberg wrote:
> Hi Ng,
>
> In my database, I have a column, containing identifiers (numbers),
> which I would like to keep unique. Some times, it is necessary to
> increment some (or all) of the number by one.
>
> Just doing
>
> update table set uniquecol=uniquecol +1
>
> generates an error.
Ahh that's ful!

WHAT ERROR!!!

On Mon, 07 Jan 2008 23:15:50 +0100, Kasper Lindberg <NoSp@m.invalid> wrote:

> Hi Ng,
>
> In my database, I have a column, containing identifiers (numbers), which
> I
> would like to keep unique. Some times, it is necessary to increment some
> (or
> all) of the number by one.
>
> Just doing
>
> update table set uniquecol=uniquecol +1
>
> generates an error.

We don't deal with 'an' error, please, when posting, include the
_specific_ _ful_ error message.

> How can I keep the unique index on my column, but stil be able to
> increment
> the values, without unnecessary overhead.?

Perhaps:
UPDATE table SET uniquecol = uniquecol + 1 ORDER BY uniquecol DESC
--
Rik Wasmus

On Jan 7, 4:15 pm, "Kasper Lindberg" <N...@m.invalid> wrote:
> Hi Ng,
>
> In my database, I have a column, containing identifiers (numbers), which I
> would like to keep unique. Some times, it is necessary to increment some (or
> all) of the number by one.
>
> Just doing
>
> update table set uniquecol=uniquecol +1
>
> generates an error.
>
> How can I keep the unique index on my column, but stil be able to increment
> the values, without unnecessary overhead.?
>
> --
> /Kasper

Sorry...

I just noticed you're doing an UPDATE to existing table. I think you
may have to do it with a script where you loop over the list, then run
a query on each item to do the update.

There's a danger though that you may hit if you have data like..

ununiquecol
----------------
1
2
3
4
5

and a constraint to make it unique. If you update 1 and add one, it
will be 2, which violates the constraint.

I think your best bet may be to create a new column called
"uniquecol2" and update that column = the value of uniquecol+1, then
drop uniquecol.

Again... hope that s. I'm not sure I'm totally following what your
goal is.

Good luck.

D.

Paul Lautman wrote:
> Kasper Lindberg wrote:
>> Hi Ng,
>>
>> In my database, I have a column, containing identifiers (numbers),
>> which I would like to keep unique. Some times, it is necessary to
>> increment some (or all) of the number by one.
>>
>> Just doing
>>
>> update table set uniquecol=uniquecol +1
>>
>> generates an error.
> Ahh that's ful!
>
> WHAT ERROR!!!

A UNIQUE violation error, I guess.

Hint: MySQL can execute UPDATE in a given order. I.e.
UPDATE TABLE SET uniq_field=uniq_field+1 ORDER BY uniq_field DESC


XL

"Axel Schwenke" <axel.schwenke@gmx.de> wrote in message
news:j9na55-fvn.ln1@xl.homelinux.org...
> Paul Lautman wrote:
>> Kasper Lindberg wrote:
>>> Hi Ng,
>>>
>>> In my database, I have a column, containing identifiers (numbers),
>>> which I would like to keep unique. Some times, it is necessary to
>>> increment some (or all) of the number by one.
>>>
>>> Just doing
>>>
>>> update table set uniquecol=uniquecol +1
>>>
>>> generates an error.
>> Ahh that's ful!
>>
>> WHAT ERROR!!!

Sorry, Don't shout. I know I forgot to specify the error, but it was late
and I had been strugling with it for too long. Guess I was too close to the
problem to give a prober description. Again, Sorry.

>
> A UNIQUE violation error, I guess.
>

Precisely

> Hint: MySQL can execute UPDATE in a given order. I.e.
> UPDATE TABLE SET uniq_field=uniq_field+1 ORDER BY uniq_field DESC
>

Exactly the stuff I was looking for.

Thanks alot for your

--
/Kasper

"DonO" <don.orban@gmail.com> wrote in message
news:5f6c2ced-81e3-4c7b-aec6-5f64889fd516@p69g2000hsa.googlegroups.com...
>
> Is there a reason you're not using the autoincrement option that MySQL
> has?
>

The (unique) column in question, decides the ordering of the records when
written to a web-page. I need to be able to rearrange the records (and
insert in the middle), on the fly, with every record having a unique
sequence-number.

The table-primary key is set as auto_increment, but this has a different use
than the unique column.

--
/Kasper

"Rik Wasmus" <luiheidsgoeroe@hotmail.com> wrote in message
newsp.t4kz4fkl5bnjuv@metallium.lan...
> On Mon, 07 Jan 2008 23:15:50 +0100, Kasper Lindberg <NoSp@m.invalid>
> wrote:
>> How can I keep the unique index on my column, but stil be able to
>> increment
>> the values, without unnecessary overhead.?
>
> Perhaps:
> UPDATE table SET uniquecol = uniquecol + 1 ORDER BY uniquecol DESC
>

That's it, Thanks

--
Kasper

On Tue, 8 Jan 2008 22:05:03 +0100, Kasper Lindberg wrote:
>
> "DonO" <don.orban@gmail.com> wrote in message
> news:5f6c2ced-81e3-4c7b-aec6-5f64889fd516@p69g2000hsa.googlegroups.com...
>>
>> Is there a reason you're not using the autoincrement option that MySQL
>> has?
>>
>
> The (unique) column in question, decides the ordering of the records when
> written to a web-page.

You can ORDER BY anything you want. It doesn't have to be the PK column.
And probably shouldn't. You may want a sort_order column for that, that
doesn't have UNIQUE on it.

> I need to be able to rearrange the records (and
> insert in the middle), on the fly, with every record having a unique
> sequence-number.

Perhaps you would be even more interested in a next_record column and
possibly a prior_record column, modeling a linked list instead. This
allows insertion of new records and reordering without renumbering.

> The table-primary key is set as auto_increment, but this has a different use
> than the unique column.

--
Christian Biblical literalists are trusting themselves to an archaic English
translation of a Latin translation of ( me here) Greek? Aramaic? source.
I wouldn't even trust a VCR manual to make it through that intact. - Dr. Dee

"Peter H. Coffin" <hellsop@ninehells.com> wrote in message
news:slrnfo8eo2.g5f.hellsop@abyss.ninehells.com...
> On Tue, 8 Jan 2008 22:05:03 +0100, Kasper Lindberg wrote:
>>
>> I need to be able to rearrange the records (and
>> insert in the middle), on the fly, with every record having a unique
>> sequence-number.
>
> Perhaps you would be even more interested in a next_record column and
> possibly a prior_record column, modeling a linked list instead. This
> allows insertion of new records and reordering without renumbering.
>

That sounds interesting.
The table in question is a menu, for my website, which I can edit on the
fly.
Can you show me an example on how to fetch the menu, using the linked-list
approach described above?

My table:

CREATE TABLE `menu` (
`menuid` int(11) NOT NULL auto_increment,
`sequence` int(11) NOT NULL default '0',
`indent` int(11) NOT NULL default '1',
`tekst` varchar(25) collate latin1_danish_ci NOT NULL default 'Link',
`pid` int(11) NOT NULL default '0',
`parm` varchar(255) character set utf8 collate utf8_danish_ci NOT NULL
default '',
`type` enum('norm','cms') collate latin1_danish_ci NOT NULL default
'norm',
PRIMARY KEY (`menuid`),
UNIQUE KEY `sequence` (`sequence`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci


--
/Kasper