List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:September 12 2007 5:49pm
Subject:Re: Question related to INSERT statement into table1 and SELECT statement from table1 to populate a column field into table1
View as plain text  
> > 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
Thread
Question related to INSERT statement into table1 and SELECT statement from table1 to populate a column field into table1Mariella Petrini12 Sep
  • Re: Question related to INSERT statement into table1 and SELECT statement from table1 to populate a column field into table1Martijn Tonies12 Sep
  • Re: Question related to INSERT statement into table1 and SELECT statement from table1 to populate a column field into table1Martijn Tonies12 Sep