Duplicate Value In Database With Same User Id

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

Answer 1

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.

Answer 2

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

Answer 3

User-742704163 posted

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

Answer 4

User269602965 posted

http://psoug.org/reference/merge.html