Updating a table starting with the first empty column in a specific row

[kaleidoscope]

Hi,
I'm struggling to find a solution to this (very likely simple) problem:

I have 2 tables (say A and B). I want to populate some rows in B with information from A. I want the update query to start from the first row were column1 is empty, downwards. Tried to use cursor but wasn't successful. Any help is much appreciated.

[gpl]

What do you mean by downwards ? There is no* default order in a table, what sort order do you really mean ?

*actually, there is the order that the rows were inserted, but if you have a clustered index then that order would override the insertion order

[ck9663]

Try something like:

UPDATE A
SET
A.COL1 = ISNULL(A.COL1, B.COL1),
A.COL2 = ISNULL(A.COL2, B.COL2),
A.COL3 = ISNULL(A.COL3, B.COL3) 
FROM TABLEA A
INNER JOIN TABLEB B ON A.KEY = B.KEY
 

Now, this code will only update your column if it's currently NULL. Technically, it will update even if it's not null. However, if it's not null, it will update with the current value.

Good Luck!!!

~~ CK

[NeoPa]

If I understand your question correctly then I'm not sure if this could be done using SQL alone.

If it were an Access question I'd say to use recordset processing in VBA. Something similar may be possible with a cursor in T-SQL, but I wouldn't know if it is. Alternatively, newer versions of MS SQL Server give the facility to link in .NET code from any of the .NET languages using the Common Language Runtime (or CLR). I'm confident this could work for you, but I'm afraid my experience stops short of being much help along those lines.

Welcome to Bytes!

[Jerry Winston]

I could tell you how to do this if I knew which table column1 belongs to. I think you're going for something like this (psudo-code):

WHILE B.lastrow = FALSE
   IF B.column1 = NULL THEN
      B.column1 = A.columnN(rowNumber)
      rowNumber++
   END IF 
WEND

please be very specific about which field(column) is NULL and from where you would like to get data to update it.