CODEWITHSUNDEEP
sql-servert-sqlduplicates
Robert Kesik
Robert Kesik

Reputation: 1

TSQL for updating TABLEA from tableB where no id's exist

I have the following table structure.

Table A
InvoiceNumber
InvoiceDate
Sku
SerialNumber

Table B
InvoiceNumber
Invoicedate
Sku
SerialNumber

Table B has valid SerialNumbers, while Table A does NOT (its blank). I would like to update Table A with table B's serial numbers.

There can be multiple records with the same invoiceNumber, Invoicedate and Sku, only serialNumber is unique.

If I do an

update tablea set serialNumber = tableb.serialNumber
where tablea.sku = tableb.sku
and tablea.invoicenumber = tableb.invoicenumber
and tablea.invoicedate = tableb.invoicedate

I end up getting duplicate serials in table a.

Sample Data

Table a

InvoiceNbr : 1 invoiceDate = 10/01/2015 sku = ABC serial = blank
InvoiceNbr : 1 invoiceDate = 10/01/2015 sku = ABC serial = blank

Table b

InvoiceNbr : 1 invoiceDate = 10/01/2015 sku = ABC serial = abc
InvoiceNbr : 1 invoiceDate = 10/01/2015 sku = ABC serial = xyz

No matter what I do I always end up with dupes in table a :|

Upvotes: 0

Views: 35

Answers (1)

shree.pat18
shree.pat18

Reputation: 21757

Try this:

update tableA
set serialNumber = b.serialNumber
from (select *, row_number() over (partition by invoicenumber, invoicedate, sku order by serialnumber) rn from tableA) a
inner join
(select *, row_number() over (partition by invoicenumber, invoicedate, sku order by serialnumber) rn from tableB) b 
on a.sku = b.sku and a.invoicenumber = b.invoicenumber and a.invoicedate = b.invoicedate and a.rn = b.rn

Demo

If I understand correctly, there are records in table B with all columns except serialNumber having same values, and your current update logic just fills table A with one of the values of serialNumber rather than a 1 to 1 update. The above solution uses row_number to create an extra identifier for each row in table B, and then uses that as an additional criteria to match the rows for updating.

Upvotes: 0

Related Questions