Update subset of data in sqlserver

Question

Hi,

I have been given the following request.

Please give 7% of the current contacts registered to each of the sales associates to the new sales associate ('Peter').

What I decided to do was to get the total records for each sales associate and calculate 7% of the records.

For example David has 200 200/7% = 14

SELECT TOP 14 ContactAssociate
FROM tb_Contact 
WHERE tb_Contact.ContactAssociate = 'David' 
ORDER BY NEWID()

Now, I can select the data but am struggling to update them; I thought this would do it but no joy.

UPDATE tb_Contact
SET ContactAssociate = 'Peter'
IN
(
SELECT TOP 14 ContactAssociate
FROM tb_Contact 
WHERE tb_Contact.ContactAssociate = 'David' 
ORDER BY NEWID()
)

Any ideas where, I'm going wrong? Any help, much appreciated.

Answer 1

PK_Of_tb_Contact - primary key in your tb_Contact table

UPDATE tb_Contact
SET ContactAssociate = 'Peter'
where PK_Of_tb_Contact
IN
(
SELECT TOP 14 PK_Of_tb_Contact
FROM tb_Contact 
WHERE tb_Contact.ContactAssociate = 'David' 
ORDER BY NEWID()
)

Answer 2

Try this:

UPDATE c
SET ContactAssociate = 'Peter'
FROM tb_Contact c
INNER JOIN (
  SELECT TOP 14 ContactAssociate FROM tb_Contact 
  WHERE tb_Contact.ContactAssociate = 'David' 
) q ON c.ContactAssociate = q.ContactAssociate

If you want to try if you are updating the records you want, you may do this:

SELECT c.*
FROM tb_Contact c
INNER JOIN (
  SELECT TOP 14 ContactAssociate FROM tb_Contact 
  WHERE tb_Contact.ContactAssociate = 'David' 
) q ON c.ContactAssociate = q.ContactAssociate

As you can see, the only change between updates or checks are the lines before the FROM clause.

Answer 3

Why not use TOP 7 PERCENT or TOP 7 PERCENT WITH TIES?

DECLARE @sample int
SET @sample = 7

UPDATE tb_Contact
SET ContactAssociate = 'Peter'
where PK_Of_tb_Contact
IN
(
SELECT TOP (@sample) PERCENT PK_Of_tb_Contact
FROM tb_Contact 
WHERE tb_Contact.ContactAssociate = 'David' 
ORDER BY NEWID()
)