Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Unanswered: Insert with a If statement. But yet not using a stored procedure. Is that possible?

    my table:

    FoodID Integer PRIMARY KEY,
    FoodName varchar(255),
    FoodDesc text

    How do I insert into this table while checking that the FoodName do not replicate? I'm aware that with a stored procedure I'm able to you the IF EXIST statement to help me solve this problem. But if I do not wish to use the stored procedure, am I able to create a SQL string to insert while checking the condition?

    Thanks in advance to the people that replied to my request! Thanks so much...

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would declare a unique constraint, something like:
    PHP Code:
    ALTER TABLE myTable
       ADD CONSTRAINT XAK01myTable UNIQUE 
    (FoodName
    which makes the database engine itself prevent duplicate values.

    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    5
    is it possible to have a check condition instead of adding in a unique key? If the foodname exist, if won't insert? Can someone help me to form a sql statement that will solve my problem? Thanks in advance!

  4. #4
    Join Date
    Jan 2004
    Posts
    26
    insert into yourTable(FoodID,FoodName,FoodDesc)
    select FoodID_Value,FoodName_Value,FoodDesc_Value from yourTable
    where not exists(select 1 from yourTable where FoodName = FoodName_Value)

    - if there is a repeat foodName,the above SQL will insert a empty rowSet just like nothing inserted(and won't increase the Identity-Column Value if your table has one).
    ......

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by DirEnBlue
    is it possible to have a check condition instead of adding in a unique key? If the foodname exist, if won't insert? Can someone help me to form a sql statement that will solve my problem? Thanks in advance!
    Unless I'm missing something, that is exactly what my previous suggestion will do. The unique constraint will guarantee that only one row will exist in your table with a particular food name.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •