inserting unique value

3 Message(s) by 2 Author(s) originally posted in mysql database

From: Rune Zedeler

Date: Thursday, June 21, 2007

I need to insert a unique value into a (not auto-increment) column.
I try

insert into idtest (val) values ((select max(val) from idtest)+1);

but I get

ERROR 1093 (HY000): You can not specify target

<hardware> A {SCSI} device that executes a command from a
	{SCSI initiator} to perform some task.  Typically the target
	is a SCSI peripheral device but the {host adapter} can also be
	a target.

	(1999-02-10)

table

<database> A collection of {records} in a {relational database}.

	(1997-06-04)

'idtest' for update
in FROM clause

- what is the correct way to do this?

Regards,
Rune

From: lark

Date: Friday, June 22, 2007

== Quote from Rune Zedeler (rz@xxxxxxxxxxx)'s article

I need to insert a unique value into a (not auto-increment) column.
I try
insert into idtest (val) values ((select max(val) from idtest)+1);
but I get
ERROR 1093 (HY000): You can not specify target table 'idtest' for update
in FROM clause
- what is the correct way to do this?
Regards,
Rune

how about this:

insert into idtest (val) (select (max(val)+1) from idtest);

let me know if it works for you.
--
POST BY: lark with PHP News Reader

From: Rune Zedeler

Date: Friday, June 22, 2007

lark skrev:

insert into idtest (val) (select (max(val)+1) from idtest);

Thanks, works! :+)

-Rune

Next Message: creating table for rails

Blogs related to inserting unique value

Indexes in MySQL
Begin with a table and the data it needs, but without any indexes except those designed to constrain the data to valid values (primary and unique indexes). Next, consider the queries that are issued against the table. ...

Predicting how long data load would take
Load Options There two main ways to load data in MySQL you can use Multiple value insert (standard mysqldump output) or LOAD DATA INFILE (–tab mysqldump output). Generally LOAD DATA can be optimized better and a bit faster because of ...

MySQL 5.1.18-beta has been released
NDB Cluster: INSERT IGNORE wrongly ignored NULL values in unique indexes. (Bug#27980: http://bugs.mysql.com/27980) * NDB Cluster: The name of the month "March" was given incorrectly in the cluster error log. ...

Merge Tables Gotcha
Query OK, 0 rows affected, 1 warning (0.03 sec). mysql> INSERT INTO t1 VALUES(1);. Query OK, 1 row affected (0.00 sec). mysql> INSERT INTO t2 VALUES(1);. Query OK, 1 row affected (0.00 sec). mysql> SELECT * FROM tm WHERE id=1; ...

Unique strings in a text field
`c` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 INSERT INTO hmm VALUES (1, 'hmm8',1) ON DUPLICATE KEY UPDATE b = IF(FIND_IN_SET('hmm8', b)>0, b, CONCAT_WS(',',b, 'hmm8')), c = c+1; ...

inserting unique value
I need to insert a unique value into a (not autoincrement) column. I try insert into idtest (val) values ((select max(val) from idtest)+1) but I get ERROR 1093 (HY000): You cant specify target table idtest for update in FROM clause wha ...