From: Martijn Tonies
Date: September 12 2007 5:45pm
Subject: Re: Question related to INSERT statement into table1 and SELECT statement from table1 to populate a column field into table1
List-Archive: http://lists.mysql.com/mysql/209034
Message-Id: <018c01c7f564$bdb88630$9902a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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