Hi All,

I have a table with a PRIMARY KEY on id field, whos
evalue is populated usin auto_increment.


CREATE TABLE `key` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`sid` smallint(4) unsigned NOT NULL DEFAULT '0',
`email` varchar(128) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB;

Question:

1)
When I INSERT a row is there any way to be able in the
same INSERT statement (without doing an UPDATE after
the insert) to populate the field `sid`, which is base
on the value that the field `id` gets (e.g. sid= MOD
(id, 20))

P.S. I cannot use the value of LAST_INSERT_ID, because
it is inappropriate in this case

P.S. I have tried to use the following, but it failed:

INSERT INTO key (sid,email) VALUES (MOD((select
max(id) from key) + 1, 20), "test@yahoo.com");
ERROR 1093 (HY000): You can't specify target table
'key' for update in FROM clause


Could you please ?

Thanks in advance for your ,

Mariella



__________________________________________________ __________________________________
Luggage? GPS? Comic books?
Check out fitting gifts for grads at Yahoo! Search
http://search.yahoo.com/search?fr=on...on+gifts&cs=bz

Hi,

> I have a table with a PRIMARY KEY on id field, whos
> evalue is populated usin auto_increment.
>
>
> CREATE TABLE `key` (
> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> `sid` smallint(4) unsigned NOT NULL DEFAULT '0',
> `email` varchar(128) NOT NULL DEFAULT '',
> PRIMARY KEY (`id`),
> UNIQUE KEY `email` (`email`)
> ) ENGINE=InnoDB;
>
> Question:
>
> 1)
> When I INSERT a row is there any way to be able in the
> same INSERT statement (without doing an UPDATE after
> the insert) to populate the field `sid`, which is base
> on the value that the field `id` gets (e.g. sid= MOD
> (id, 20))

Sounds like an excellent case for a TRIGGER.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> > I have a table with a PRIMARY KEY on id field, whos
> > evalue is populated usin auto_increment.
> >
> >
> > CREATE TABLE `key` (
> > `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> > `sid` smallint(4) unsigned NOT NULL DEFAULT '0',
> > `email` varchar(128) NOT NULL DEFAULT '',
> > PRIMARY KEY (`id`),
> > UNIQUE KEY `email` (`email`)
> > ) ENGINE=InnoDB;
> >
> > Question:
> >
> > 1)
> > When I INSERT a row is there any way to be able in the
> > same INSERT statement (without doing an UPDATE after
> > the insert) to populate the field `sid`, which is base
> > on the value that the field `id` gets (e.g. sid= MOD
> > (id, 20))
>
> Sounds like an excellent case for a TRIGGER.

Sorry, I think I'm wrong here --

This would only work if NEW.ID already has a value when
the BEFORE INSERT trigger get's called, but I think it has
not, as the "id" column would only be filled when doing the
actual insert, and not before.

You might wanna try that out.

AutoInc stinks. Always does :-)



Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com