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?
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?
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.