• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

help on sql query

 
Ayub ali khan
Ranch Hand
Posts: 395
2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 572
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 395
2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 118
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 395
2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 395
2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1511
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic