Geek asks and answers

With MySQL, how do I insert into a table, provided that this value does not exist in the same table?

insert into student (FirstName,AGE,CITYID) values('guna','26','1')

select * from student WHERE FirstName!='guna';

This request shows an error. I cannot make the FirstName column unique. Please give an idea other than this.

thank

+3
source share
2 answers
INSERT INTO student ( ....)
WHERE FirstName NOT IN (SELECT FirstName FROM student)

After review and testing:

INSERT INTO student 
    (FirstName, age, cityid)
SELECT 
    'guna','26','1'
FROM student -- any table name will do
WHERE 'guna' NOT IN 
(
    SELECT FirstName 
    FROM student
)
LIMIT 1 -- required because all rows will qualify if 
        -- WHERE clause is satisfied
+3
source

You can add a unique index to this table, which will do the same for you.

ALTER TABLE student ADD UNIQUE <name_of_index>[optional] (FirstName);

EDIT:

If you can not use a unique index.

One soln I can think of uses compound expressions - http://dev.mysql.com/doc/refman/5.0/en/if-statement.html

+2
source

More articles:


All Articles