SQLTeam.com | Weblogs | Forums

Unable to update

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

I'm trying to understand what you are asking for, but not clear at all. You provided 2 rows in the table which is fine. What are you expecting for results? In the example you provided, no rows will be updated since @partial is not null. Are you looking for this in a proc where you'll pass in the parameters you are searching for or are you looking for a set-based solution?

Hi @mike01

thanks for replying,

am trying to update table @temp and want to validate, in any of the case passportNo, AdhaarNo and Others should not update duplicates in table @temp.

I'm still confused. If those 3 fields are the key, then why not use them in the update instead of ID?

@mike01 let me explain you in other way passport,adhaar and voteid are unique identification number along with that we have some general columns.

if already any of the person has this data while updating same should not occur to other person.

My point is in any way around dupes should not enter via update statement

User may enter incorrect passport or adhaar or voteid later he realized and want to update the correct data then id will be helpful.

Hope the above content may helpful

so, based on your data, pp 111 and adhaar 123 and VoteID 456 exist in the table already and have ID = 1. Would your update actually update the record?? What if the data were different where that combination was ID =2 in the table and you passed in ID = 1? In that case, you wouldn't want to update it, correct?

if not exists(select 1 
                   from @temp 
                where PassportNo = @pp
                    and Adhaar = @adhaar
                     voteid = @voteid
being
update @temp ---etc
end

maybe?

You have to test 3 scenarios: the 3 fields aren't in the db or they are and belong to the same ID

DDL and Data:

Summary

Create table #Temp
(
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')

scenario 1 - 3 fields match and ID matches - update

Summary

declare @pp varchar(1000) = '111'
declare @adhaar varchar(1000) = '123'
declare @voteid varchar(1000) = '456'
declare @age int = 33
declare @name varchar(1000) = 'ccc'
declare @nationality varchar(1000) = 'American'
declare @id int = 1

if not exists (select 1 from #temp
where PassportNo = @pp
and adhaar = @adhaar
and voteid = @voteid)
or
exists (select 1 from #temp
where PassportNo = @pp
and adhaar = @adhaar
and voteid = @voteid
and id = @id)
begin
select 'Update Record'
end
else
begin
select 'No Update'
end
go

scenario 2 - 3 fields match but ID doesn't - no update

Summary

declare @pp varchar(1000) = '111'
declare @adhaar varchar(1000) = '123'
declare @voteid varchar(1000) = '456'
declare @age int = 33
declare @name varchar(1000) = 'ccc'
declare @nationality varchar(1000) = 'American'
declare @id int = 2

if not exists (select 1 from #temp
where PassportNo = @pp
and adhaar = @adhaar
and voteid = @voteid)
or
exists (select 1 from #temp
where PassportNo = @pp
and adhaar = @adhaar
and voteid = @voteid
and id = @id)
begin
select 'Update Record'
end
else
begin
select 'No Update'
end
go

scenario 3 - 1 or more of the 3 fields don't match. Update record

Summary

declare @pp varchar(1000) = '112'
declare @adhaar varchar(1000) = '123'
declare @voteid varchar(1000) = '456'
declare @age int = 33
declare @name varchar(1000) = 'ccc'
declare @nationality varchar(1000) = 'American'
declare @id int = 1

if not exists (select 1 from #temp
where PassportNo = @pp
and adhaar = @adhaar
and voteid = @voteid)
or
exists (select 1 from #temp
where PassportNo = @pp
and adhaar = @adhaar
and voteid = @voteid
and id = @id)
begin
select 'Update Record'
end
else
begin
select 'No Update'
end
go