sql server - SQL Query to update department -

i have special scenario update department without having conflicts existing records.

can tell me how write query it?

here need.

table dept:

deptid             deptname -----------------------------     d001               accounts d002               hr d003               dev d004               support 

now, want update 1 of existing records , make sure should not allow duplicates (either deptid or deptname , both).

ex:

• case 1: when try update d001, should not give deptid d002, d003 or d004 existing.

• case 2: when try edit deptname, should not accept of existing deptnames

you'd better off storing department identifier int / primary key auto increments. take care of id's being unique.

alter table dept  add deptid int not null identity (1,1) primary key 

if want format d001, on values, can add when retrieve data:

select  deptid,         'd' + right('000' + cast(deptid varchar(3)), 3)    dept 

then need worry department names being unique.

with department names, firstly should have application logic prevents duplicate values being added , secondly, can perform check when attempt add new value:

declare @depttoadd varchar(20) = 'cleaning'  if not exists (select 1 dept deptname = @depttoadd) begin -- insert code if not exist   insert dept (deptname)   values (@depttoadd) -- or update   update dept   set deptname = @depttoadd   deptid = 123 end 

this insert or update value if doesn't exist.

on top of can enforce unique constraint.