help on sql query

[Ayub ali khan]

How to write a query which will insert a particular string only if it is not already in the database.

Example: If the database has a column name as tech with values : HTML,JAVA,COBOL.

when I try to insert say JSCRIPT then it should insert it only if it is not already in the database. I am using MS SQL 2000.

Thanks in advance

[Ali Gohar]

hi,

first write the query to check whether the value is already in the table or not using select * from table where value=yourValue, if resultset brings any record, means the value is already there and if not then you can insert one.

Otherwise apply a unique constraint on the column if you don't want the column to contain 2 or more same values.

[Ayub ali khan]

Thanks Ali!!

I could do this using JDBC. However I would like to know if there is an alternate method which can be used using nested queries?

[Sri Ram]

One Hint,
U could use subqueries with "Exist". Try using this. Search for Exist in sql server book online. You could get extensive write up on this with examples.

[Ayub ali khan]

Thanks for the clue!!
I understood the usage of EXISTS in SELECT statement.

However I am not able to build the complete query to insert a value which is not a duplicate value in the database.

Thanks

[suresh koutam]

I dont think you are getting it right..if u have the data already in the table, it is going to be an update not an insert ...so you have to use and update statement...

assume u want to insert COBOL
UPDATE table SET table_column_value = COBOL WHERE NOT EXISTS (SELECT * FROM table WHERE column_value = COBOL)

i think this should work...

[Ayub ali khan]

Hi Suresh,

That query would replace all the entries which are not cobol to cobol. However my intension was to add the new entry after confirming that its not already present in the database.

Suppose if I had java,C#,VB. If I use your query it will replace everything with cobol. However what I want is if I enter cobol it should be inserted and avoid duplicates. When cobol is entered again, it should not accept it.

one of my friend told I may have to write a stored procedure for this... I am trying to find if there any other alternate solution like a nested query?

Thanks for helping me out.

[Bosun Bello]

Someone has already posted a way to do it:

Select the value you are looking for, if it's not there then do the insert, alse move on...

1. Try to select the value you are looking for
2. if it's not found, insert the record.

I think thisis simple enough.