Hi all,
can anyone please help me on this,
I m stuck where i want update the table @temp
condition is passport , adhaar and voteid if exists to any of the person same should not allow to other person in table but if i want to update for same/different person other fields like (age , name and Nationality ) it should allow.
thing is like table should not allow duplicate passport , adhaar and voteid.
declare @pp varchar(1000) = '111'
declare @adhaar varchar(1000) = '123'
declare @voteid varchar(1000) = '456'
declare @age int = 22
declare @name varchar(1000) = 'ccc'
declare @nationality varchar(1000) = 'india'
declare @all int
declare @partial int
declare @id int = 1
declare @temp table
(
id int identity(1,1),
PassportNo varchar(1000),
Adhaar varchar(1000),
voteid varchar(1000),
name varchar(1000),
Age int,
Nationality varchar(1000)
)
insert into @temp (PassportNo,Adhaar,voteid,name,age,Nationality)
values('111','123','456','abc',22,'india')
insert into @temp (PassportNo,Adhaar,voteid,name,age,Nationality)
values('888','999','897','xyz',21,'india')
--select * from @temp
select @all = id from @temp where PassportNo = @pp and Adhaar = @adhaar and voteid = @voteid and age = @age and name = @name and Nationality = @nationality
select @partial = id from @temp where PassportNo = @pp and Adhaar = @adhaar and voteid = @voteid
if(@all is null and @partial is null)
begin
update @temp
set PassportNo = @pp ,
Adhaar = @adhaar ,
voteid = @voteid ,
age = @age ,
name = @name ,
Nationality = @nationality
where Id = @id
select 1 as [update result]
end
else
begin
select 0 as [update result]
end
--select * from @temp