Insert Only distinct values for one column

Question

id   |   a   |      b      |
-----------------------------
1    |  xyz  |     abc     |

This is the structure of my table.The column id is AUTO_INCREMENT. I want to insert only those data in the table whose 'b' is not abc. For example-

This should not insert anything:

insert into table_name(`a`,`b`) values("xyz","abc");

However this should insert new record:

That is it should only check column b.

insert into table_name(`a`,`b`) values("xyz","zzzz");

How do i do this?

@tereško what did you mean by that? I have already created correct table. — ASHUTOSH, Aug 18 '12 at 08:38
If the table is missing `UNIQUE` constraint, then it is NOT correct. — tereško, Aug 18 '12 at 08:48
No it is not missing. I checked thrice before posting the question — ASHUTOSH, Aug 18 '12 at 08:53
Would you kindly post the output for `SHOW CREATE TABLE tableName ? — The Disintegrator, Aug 18 '12 at 09:08
Ok I got this.Column `b` was of `text` type and `unique` was set.I then changed it to varchar and now its working! Thank you guys! — ASHUTOSH, Aug 18 '12 at 09:11

score 1 · Answer 1 · answered Aug 18 '12 at 06:54

1

You could add, UNIQUE constraints on your column b

When a duplicate value is served for insertion, it would throw an SQL exception, instead of inserting, you can handle this exception in PHP

answered Aug 18 '12 at 06:54

s_s_

471
5
20

YA I have set the column b to unique but still same data is inserted. – ASHUTOSH Aug 18 '12 at 06:57
@ASHUTOSH could you post the Table structure, which would help us to understand where you are going wrong, also include the constraints – s_s_ Aug 18 '12 at 06:59

score 1 · Answer 2 · edited May 23 '17 at 12:12

1

Your options:
1.Use unique() in your insert query on column b
2.Run a quick SELECT query to check for an existing column (least preferred) It could also be embeded in your update query with an if clause

Reference How to 'insert if not exists' in MySQL?

edited May 23 '17 at 12:12

Community

1
1

answered Aug 18 '12 at 06:57

Jared Drake

982
4
12

1

please provide us an example on using `unique()` in insert query, i am interested – s_s_ Aug 18 '12 at 07:03

Insert Only distinct values for one column

2 Answers2