So, I’ve been doing a lot of SQL Scripting lately and I’ve come to a stop when I hit a problem I have never hit before. I had to update ColumnA of TblA with ColumnB of TblB.

So my initial thought was to do an INNER JOIN UPDATE. So I started typing:

UPDATE a
SET a.ColumnA = b.ColumnB
FROM TblA a
INNER JOIN TblB b

And then it hit me. How do I join them? They don’t have any type of relationship. Started thinking and talking to Edgar and decided to use ROW_NUMBER() to join both tables.

UPDATE a
SET a.ColumnA = b.ColumnB
FROM 
(
    SELECT *, ROW_NUMBER() OVER(ORDER BY ColumnX) RowNum
    FROM TblA
) a
INNER JOIN
(
    SELECT *, ROW_NUMBER() OVER(ORDER BY ColumnY) RownNum
    FROM TblB
) b on b.RowNum = a.RowNum

So as you can see, since I didn’t care about which ColumnB to use for ColumnA I simply joined using RowNum just to get a one to one relationship. It is also important to notice that both tables contain the same number of rows.

Happy TSQLING!