locked
Duplicate Value In Database With Same User Id RRS feed

  • Question

  • User-742704163 posted

    Hi All,

    I have created a table with attributes like s_no , user_name , Phone_number , user_ID ........ and insertion divided into many parts so first query i used is insert and then update where user_ID matches.
    and i use query to insert like (insert into table (user_name , Phone_number , user_ID where user_ID =textbox1.text)
    and for the rest of the attributes update where user_ID = @user_ID
    something like that but the problem is if user again use the same user_ID to edit something in database there is again a new row created with the same user_ID and for this scenario user_ID must be unique and if any user again use the same user_ID then the new data will be update in the same user_ID instead of new row created. I know there must be query like

    if this then

    insert

    else

    update

    but the thing is i do'nt have any idea about it to use insert and update at the same place.

    i want to use procedure for solving this problem.

    Any help will be appropriated.
    Thanks

    Saturday, March 10, 2012 5:53 AM

Answers

  • User-1907569630 posted

    Hi tnitin55

    Please add primary key constraint to user_ID coloumn and 

    write store procedure like,

    create proc InsertOrUpdate

    (

    @user_ID int,

    --parameters .

    )

    as

    update tablename set (parameters to columns) where user_ID=@user_ID

    if(@@rowcount=0)

    begin

    insert into tablename

    {

    --column names

    }

    values

    {

    --parameters

    }

    set @user_ID=scope_identity();

    end

    select @user_ID

    Hope this will helps to you,

    Thanks,

    SCJ.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 10, 2012 6:01 AM

All replies

  • User-1907569630 posted

    Hi tnitin55

    Please add primary key constraint to user_ID coloumn and 

    write store procedure like,

    create proc InsertOrUpdate

    (

    @user_ID int,

    --parameters .

    )

    as

    update tablename set (parameters to columns) where user_ID=@user_ID

    if(@@rowcount=0)

    begin

    insert into tablename

    {

    --column names

    }

    values

    {

    --parameters

    }

    set @user_ID=scope_identity();

    end

    select @user_ID

    Hope this will helps to you,

    Thanks,

    SCJ.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 10, 2012 6:01 AM
  • User269602965 posted

    Use MERGE Clause

    If the user_id already exists, it updates your columns of choice

    If it is a new row based on userid, it inserts new row

    Saturday, March 10, 2012 5:29 PM
  • User-742704163 posted

    Thanks for the reply but can you give any example or any link to use merge clause.

    Monday, March 12, 2012 7:56 AM
  • Monday, March 12, 2012 11:00 AM