Incorrect syntax near the keyword Where. I want to prevent duplicate entry of one of the columns

Question

Please help me. I want to insert record in my database that prevents duplicate entries while inserting a record. Here's my code:

INSERT INTO Items_Tbl(Room_ID, Date_Purchased,Pc_Number)
Values('1', '2019-01-01', '1')
WHERE NOT EXISTS(Select PC_Number FROM Items_Tbl WHERE Room_ID = 1)

I don't want PC_Number to accept same entries.

Then what is the correct query to prevent duplicates on one of my column 'PC_Number' ? — Annie, Nov 11 '19 at 15:25
SQL is not a database. SQL is the language used to query the database. Database is software like MySql or SqlServer. The syntax to use could be different for each database system — Steve, Nov 11 '19 at 15:34
https://stackoverflow.com/questions/11906506/sql-server-2008-if-not-exists-insert-else-update — Steve, Nov 11 '19 at 15:38

score 0 · Answer 1 · answered Nov 11 '19 at 17:18

One way to do it is as such:

IF NOT EXISTS(Select PC_Number FROM Items_Tbl WHERE Room_ID = 1)
    INSERT INTO Items_Tbl(Room_ID, Date_Purchased,Pc_Number)
    Values('1', '2019-01-01', '1')

However, since you are using SQL Server, it's much better to create a unique, non-clustered index on the target table for the field PC_Number. You can also create a query/stored procedure separately to check if the PC_Number exists, if you need to reuse it elsewhere.

Incorrect syntax near the keyword Where. I want to prevent duplicate entry of one of the columns

1 Answers1