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.
SQL Undercover 