CODEWITHSUNDEEP
sql-server-2012
Aqeel Haider
Aqeel Haider

Reputation: 633

SQL column duplicate value count

I have a Student table. Currently it has many columns like ID, StudentName, FatherName, NIC, MotherName, No_Of_Childrens, Occupation etc.

I want to check the NIC field on insert time. If it is a duplicate, then count the duplicated NIC and and add the count number in No_of_Children column.

What is the best way to do that in SQL Server?

Upvotes: 1

Views: 73

Answers (1)

JosephStyons
JosephStyons

Reputation: 58755

It sounds like you want an UPSERT. The most concise way to accomplish that in SQL (that I know) is through a MERGE operation.

declare @students table
(
  NIC int
 ,No_Of_Childrens int
);

--set up some test data to get us started
insert into @students
      select 12345,1
union select 12346,2
union select 12347,2;

--show before
select * from @students;

declare @incomingrow table(NIC int,childcount int);
insert into @incomingrow values (12345,2);

MERGE 
  --the table we want to change
  @students AS target  
USING 
  --the incoming data
  @incomingrow AS source
ON 
  --if these fields match, then the "when matched" section happens.
  --else the "when not matched".
  target.NIC = source.NIC
WHEN MATCHED THEN 
  --this statement will happen when you find a match.
  --in our case, we increment the child count.
  UPDATE SET NO_OF_CHILDRENS = no_of_childrens + source.childcount
WHEN NOT MATCHED THEN 
  --this statement will happen when you do *not* find a match.
  --in our case, we insert a new row with a child count of 0.
  INSERT (nic,no_of_childrens) values(source.nic,0);

--show the results *after* the merge
select * from @students;

Upvotes: 2

Related Questions