CODEWITHSUNDEEP
c#sqlsql-server
Carl Binalla
Carl Binalla

Reputation: 5401

Updating a null row without anything unique for that row

I have the following data:

[Receipt]   [Date]     [Barcode]    [Quantity]  [Supplier]  [DeliveryID]
  0001    01/01/2000    0000001         5        Grocery        NULL
  0001    01/01/2000    0000002         7        Grocery        NULL
  0001    01/01/2000    0000571         2        Grocery        NULL
  0002    01/01/2000    0000041         5         Grocey        NULL
  0002    01/01/2000    0000701        10         Grocey        NULL
  0003    01/01/2000    0000001         9        Groceri        NULL

What can I do to put an incrementing value to the DeliveryID?

As you can see, there is nothing unique that can be used to distinguish a row from another row. There is a large chance that a similar data like any of those rows may be entered again.

Unfortunately, I cannot delete the table and create a new one for the sake of the new column.

I tried counting all the null rows first,

SELECT COUNT(*) as TotalCount FROM dbo.Delivery WHERE DeliveryID IS NULL

And create a for loop to update.

But I realized that after the first loop, all null will be replaced by 1, instead of updating each row per loop.

I have thought of combining the Receipt and Supplier to become a unique value, but as I said earlier, there's a chance that a similar data may be entered, thus creating a duplicate row, losing the uniqueness of the row

Upvotes: 1

Views: 59

Answers (3)

Prashant Bamania
Prashant Bamania

Reputation: 99

Add column with identity that would help you

like

 alter table tableName add  DeliveryID int Identity(1,1)

Upvotes: 0

JohnHC
JohnHC

Reputation: 11195

Use a row_number

with D as
(
    select Receipt, Date, Barcode, DeliveryID, row_number() over(order by receipt , Date, Barcode) as rn
    from delivery
    )

update D
set DeliveryID = rn
where DeliveryID is null

You can even partition by receipt to provide a per-line within the receipt group:

with D as
(
    select Receipt, Date, Barcode, DeliveryID, row_number() over(partition by receipt order by Date, Barcode) as rn
    from delivery
    )

update D
set DeliveryID = rn
where DeliveryID is null

Upvotes: 2

xCloudx8
xCloudx8

Reputation: 721

You could use these columns:

[Receipt]   [Date]     [Barcode]    [Quantity]

as a PrimaryKey so you can identify the rows you need.

Or for the column [Delivery ID] you could use the IDENTITY function:

Upvotes: 1

Related Questions