List:General Discussion« Previous MessageNext Message »
From:Mariella Petrini Date:September 12 2007 5:40pm
Subject:Question related to INSERT statement into table1 and SELECT statement from table1 to populate a column field into table1
View as plain text  
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@stripped");
ERROR 1093 (HY000): You can't specify target table
'key' for update in FROM clause


Could you please help ?

Thanks in advance for your help,

Mariella



      ____________________________________________________________________________________
Luggage? GPS? Comic books? 
Check out fitting gifts for grads at Yahoo! Search
http://search.yahoo.com/search?fr=oni_on_mail&p=graduation+gifts&cs=bz
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