PHWinfo - Afficher un message - Question related to INSERT statement into table1 and SELECT statement from table1 to populate a column field into table1

12/09/2007, 18h45

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

12/09/2007, 18h45	#2
Martijn Tonies Messages: n/a Hébergeur:	Re: Question related to INSERT statement into table1 and SELECT statement from table1 to populate a column field into table1 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