Filling Missing ID Codes In SQL

This is going to be a bit of a brain storming post that comes from an interesting question that I was asked today…

“I’ve got a table with a ID code field, now some of the rows have a value in that field and some are NULL. How can I go about filling in those NULL values with a valid code but at the same time avoid introducing duplicates?”

This person had had a good crack at solving this but had hit a problem. Taking a look at how they had gone about solving this, I could see their logic but their code was overly complex and full of cursors and loops. Now, I’m not against cursors when they’re used in the right context but this was most certainly one of those classic “cursors are bad and inefficient” moments.

That got me thinking, is there a better way that this could be solved?

The Problem

Before I go any further, let’s take a quick look at the problem. Take a look at my ‘albums’ table, it holds an album name and a code for that album (the real world example was far more complex than this, but you should get the point).

As you can see, the table has a bunch of missing ID codes. Now the IDs I’ve used here are numeric, but they could be any sort of alpha numeric code.

So how do we go about filling in those blanks?

My Solution

For the solution that I’ve come up with, we first need to create a temp table which is going to hold enough valid codes for each row of the table. For now don’t worry about generating code that already exist in the ‘albums’ table, we’ll deal with those later.

CREATE TABLE #Codes
(ID INT NOT NULL IDENTITY(1,1))

INSERT INTO #Codes DEFAULT VALUES
GO 10

SELECT ID FROM #Codes

So now we have a list of all possible ID codes and notice, we have got codes that are already assigned in the base table. We can deal with those pretty easily…

SELECT ID
FROM #Codes
WHERE #Codes.ID NOT IN (SELECT ID FROM albums WHERE albums.ID IS NOT NULL)

But how do we go filling in those blank codes? You’re going to need a couple of CTEs and a little trick I blogged a while back in order to join them https://sqlundercover.com/2017/06/26/joining-datasets-side-by-side-when-theres-no-common-key/


WITH albumsCTE (ID, Title, RowNo)
AS
(SELECT ID, Title, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowNo
FROM albums
WHERE ID IS NULL),

CodesCTE (ID, RowNo)
AS 
(SELECT ID,  ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowNo
FROM #Codes
WHERE #Codes.ID NOT IN (SELECT ID FROM albums WHERE albums.ID IS NOT NULL))

UPDATE albumsCTE
SET ID = CodesCTE.ID
FROM CodesCTE
WHERE albumsCTE.RowNo = CodesCTE.RowNo

Run that in and now let’s check out our original table,

And there we have it, all our missing codes have been filled in.

That’s the solution that I came up with during the drive home, if you’ve got a clever solution to the problem, I’d love to hear about it.

One thought on “Filling Missing ID Codes In SQL

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: