CODEWITHSUNDEEP
mysql
bhuvana
bhuvana

Reputation: 53

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

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

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

This query showing error.I can't make FirstName column as unique. Please give an idea other than this.

Thanks

Upvotes: 3

Views: 2865

Answers (2)

dkretz
dkretz

Reputation: 37645

INSERT INTO student ( ....)
WHERE FirstName NOT IN (SELECT FirstName FROM student)

After revision 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

Upvotes: 3

Jai
Jai

Reputation: 3609

You can add a unique index on that table which will do the same for you

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

EDIT:

If you cant use a unique index..

One soln i can think of is using compound statements - http://dev.mysql.com/doc/refman/5.0/en/if-statement.html

Upvotes: 2

Related Questions