3
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

Harry Joy
  • 58,650
  • 30
  • 162
  • 207
bhuvana
  • 53
  • 2
  • 5
  • mark your code in code block.. please.. and what is the error you are getting exactly? – Muhammad Ummar May 16 '11 at 07:02
  • possible duplicate of [mysql insert if row does not exist already in a table with NO UNIQUE FIELDS](http://stackoverflow.com/questions/1969121/mysql-insert-if-row-does-not-exist-already-in-a-table-with-no-unique-fields) – Shakti Singh May 16 '11 at 07:03
  • Duplicate (closer) to this question: http://stackoverflow.com/questions/5038040/mysql-make-a-field-unique – ypercubeᵀᴹ May 16 '11 at 07:09

2 Answers2

3
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
dkretz
  • 37,399
  • 13
  • 80
  • 138
  • Correct. Maybe also add this answer to the [mysql insert if row does not exist already in a table with NO UNIQUE FIELDS](http://stackoverflow.com/questions/1969121/mysql-insert-if-row-does-not-exist-already-in-a-table-with-no-unique-fields)? – Konerak May 16 '11 at 07:38
  • INSERT INTO student (FirstName,AGE,CITYID) values('guna','26','1') WHERE FirstName NOT IN (SELECT FirstName FROM student) ,this query also showing an error – bhuvana May 16 '11 at 08:27
  • `WHERE NOT EXISTS` would probably be faster? – Konerak May 17 '11 at 12:40
2

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

Jai
  • 3,549
  • 3
  • 23
  • 31
  • I think the question said `I can't make FirstName column as unique`? – Konerak May 16 '11 at 07:08
  • Err.... either I didnt read it or it got added later? Anyways.. if thats what is needed, then le dorfier has the answer. – Jai May 16 '11 at 07:37