SQL Server - Uniquely update one table based on category records in another table

Question

I have one table (customerselection) that lists customer numbers and promo codes:

CustomerNumber | Promo
12345          | ABCDEF
54321          | BCDEFG
22334          | BCDEFG
54678          | BCDEFG
23454          | ABCDEF

And another table (certificates) that lists Certificates, Promos, and CustomerNumbers, but CustomerNumber is initially NULL:

Certificate | Promo  | CustomerNumber
1111111111  | ABCDEF | NULL
2222222222  | BCDEFG | NULL
3333333333  | BCDEFG | NULL
4444444444  | ABCDEF | NULL

What I need to do is uniquely assign each Certificate in the second table to a customer in the first table. The promos need to match. Update the second table with a customer number from the first, only to one record, with a matching promo.

I've done this in the past with a VB program - loop over records in the first table, update the first free record for the matching promo in the second table. But that program takes too long to run (there are usually around 600,000 records).

Is there any way to do this right in SQL? Accomplish the task with an awesomely convoluted (or even awesomely simple) SQL query? (Or SQL Server functionality?)

UPDATE There are many certificates per each promo. And many customers per promo.

But each customer can be assigned to only one certificate.

UPDATE 2 Let's call the first table customerselection and the second table certificates.

customerselection is a selection of customers that we want to assign certificates to.

certificates is a pool of certificates that can be assigned.

score 1 · Answer 1 · answered Aug 24 '13 at 10:54

1

Try This

UPDATE
    B 
SET
    B.CustomerNumber = A.CustomerNumber
FROM
    TableB B
INNER JOIN
    TableA A
ON 
    B.Promo = A.Promo

answered Aug 24 '13 at 10:54

Gayathri L

1,407
11
13

bwt · Accepted Answer · 2013-08-24T15:16:14.493

If I understand correctly you want to match certificates and customer so that every customer and every certificate appears once (for each promo). i.e. for a given promo

       custD, custR, custA
cert2    *
cert1           *
cert3                  *
cert6
cert7

The important thing being that each row and each column have at most one match.

update certificates
set CustomerNumber = t_cust.CustomerNumber
from certificates,    
( select Promo, CustomerNumber,
    row_number() over (partition by Promo order by CustomerNumber) as order_cust
  from customerselection
) t_cust,
( select Promo, Certificate,
    row_number() over (partition by Promo order by Certificate) as order_cert
  from certificates 
) t_cert
where t_cust.Promo = t_cert.Promo
  and t_cust.order_cust = t_cert.order_cert
  and certificates.Certificate = t_cert.Certificate

fixed, forgot somes commas and table names was not consistent, sorry — bwt, Aug 24 '13 at 15:18

Bren · Answer 3 · 2013-08-24T11:51:36.080

0

Calling tables as A and B, and from your question i understand there might be more than one certificate for each promo in table B. So i selected first record with lower certificate number from B and matched it with A. And updated B's customer number accordingly.

Here is the sqlfiddle for the solution i posted http://www.sqlfiddle.com/#!6/726a6/30

   UPDATE B
   SET B.CustomerNumber = A.CustomerNumber
   FROM A,
  (SELECT * FROM (
     SELECT b.*, ROW_NUMBER ( ) OVER ( partition BY Promo ORDER BY certificate ) AS row_num
             FROM B b

  ) t WHERE row_num = 1) AS  r

    WHERE A.Promo=r.promo
and b.CustomerNumber is null;

edited Aug 24 '13 at 11:51

answered Aug 24 '13 at 10:55

Bren

2,148
1
27
45

In the WHERE clause, do I need to explicitly exclude a certificate that already has a customernumber assigned? Or does the database handle that since it is all one operation? – Greg Holmes Aug 24 '13 at 11:31
@GregHolmes Well, yes you need to, i assumed all the customer numbers are empty. Will revise my answer in a couple of minutes – Bren Aug 24 '13 at 11:49
Edited the answer, i think that would do. – Bren Aug 24 '13 at 11:51
I updated the fiddle with more representative data ... it almost but doesn't work. Each customer can only get one certificate. http://www.sqlfiddle.com/#!6/53aa1/1/0 – Greg Holmes Aug 24 '13 at 12:54
Right. Just checked it out. Seems like there is more customers per promo. This would change the answer. Will have another look at it and edit my answer. – Bren Aug 24 '13 at 13:34

bummi · Answer 4 · 2013-08-24T15:28:44.150

Since the question was updated, I have to update my answer too

Declare @c table (CustomerNumber int, Promo Varchar(50))
Insert into @c
SELECT 12345 ,'ABCDEF'
UNION SELECT 54321,'BCDEFG'
UNION SELECT 54321,'XXXXXX'
UNION SELECT 77777,'XXXXXX'


Declare @ce table (Certificate bigint,Promo Varchar(50), CustomerNumber int)
insert into @ce
SELECT 1111111111,'ABCDEF',NULL
UNION SELECT 2222222222,'BCDEFG',NULL
UNION SELECT 3333333333,'XXXXXX',NULL
UNION SELECT 4444444444,'ABCEDF',NULL


Update ce set CustomerNumber = z.CustomerNumber
from
(
Select CustomerNumber,Certificate from
(select *,ROW_NUMBER() over (order by CustomerNumber) as rn
from
(
Select DISTINCT CustomerNumber from @c
) x)x
JOIN
(Select *,ROW_NUMBER() over (order by Certificate) as rn from
(
Select DISTINCT Certificate
from @ce) y
) y on x.rn=y.rn
) z join @ce ce on ce.Certificate=z.Certificate

Select * from @ce

score 0 · Answer 5 · answered Aug 24 '13 at 11:37

You need to assign one customer to each promo and be sure that a customer does not get used twice for the same promo.

The key is to enumerate both tables by promo. Then do the match by number:

with toupdate as (
      select c.*, row_number() over (partition by Promo order by newid()) as seqnum
      from Certificates c
     ),
     customers as (
      select cs.*, row_number() over (partition by Promo order by newid()) as seqnum
      from customerselection cs
     )
update toupdate
    set CustomerNumber = customers.CustomerNumber
    from toupdate join
         customers
         on toupdate.promo = customers.promo and
            toupdate.seqnum = customers.seqnum;

This will ensure that one customer does not get assigned twice to the same promo, unless the customer has two rows in the customerselection table.

I wasn't clear enough in my question ... each customer needs to get assigned to one and only one certificate. The promo does need to match, but the ultimate one to one is the customer to the certificate. — Greg Holmes, Aug 24 '13 at 13:00

SQL Server - Uniquely update one table based on category records in another table

5 Answers5