469,307 Members | 1,681 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,307 developers. It's quick & easy.

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

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.
Aug 4 '10 #1
4 2040
gpl
152 100+
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
Aug 5 '10 #2
ck9663
2,878 Expert 2GB
Try something like:

Expand|Select|Wrap|Line Numbers
  1. UPDATE A
  2. SET
  3. A.COL1 = ISNULL(A.COL1, B.COL1),
  4. A.COL2 = ISNULL(A.COL2, B.COL2),
  5. A.COL3 = ISNULL(A.COL3, B.COL3) 
  6. FROM TABLEA A
  7. INNER JOIN TABLEB B ON A.KEY = B.KEY
  8.  
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
Aug 5 '10 #3
NeoPa
32,173 Expert Mod 16PB
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!
Aug 5 '10 #4
Jerry Winston
145 Expert 100+
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):
Expand|Select|Wrap|Line Numbers
  1. WHILE B.lastrow = FALSE
  2.    IF B.column1 = NULL THEN
  3.       B.column1 = A.columnN(rowNumber)
  4.       rowNumber++
  5.    END IF 
  6. WEND
please be very specific about which field(column) is NULL and from where you would like to get data to update it.
Aug 10 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Carmine | last post: by
1 post views Thread by sho_nuff | last post: by
2 posts views Thread by GroZZleR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.