sql server - How to update a series of columns starting at the next blank column group in T-SQL? -

i trying work out best way of updating group of columns in single table, starting @ next blank group.

a simplified table structure case be:

+--------+-----------------+----------+-----------+----------+-----------+----------+-----------+ | itemid | itemdescription | prodid01 | prodqty01 | prodid02 | prodqty02 | prodid03 | prodqty03 | +--------+-----------------+----------+-----------+----------+-----------+----------+-----------+ |      1 | item 1          | abc123   |         1 | def456   |         2 |          |           | |      2 | item 2          | ghi789   |         3 |          |           |          |           | |      3 | item 3          | def456   |         2 |          |           |          |           | +--------+-----------------+----------+-----------+----------+-----------+----------+-----------+                

what need specific prodid in table , if found add in new values end group of columns populate data.

so second data table like:

+--------+--------+--------+ | prodid | newid  | newqty | +--------+--------+--------+ | def456 | xyz012 |      3 | +--------+--------+--------+ 

in case if finds prodid of def456 populate main table in last blank column available.

so in example data item 1 have newid , newqty values populated in set 03 , item 3 have values populated in set 02.

i hope makes sense, in actual data table working there on 20 column groups looking see way find last empty column group , populate data on end when needed.

as quick note on if helps prodidxx value populated if there data column group.

thanks.

update table [table1] 

the first column simplest:

set prodid01 = (case when prodid01 not null prodid01 else  (select newid [table2] [table2].prodid = [table1].prodid01))  set prodqty1 = (case when prodid01 not null prodqty1 else (select newqty [table2] [table2].prodid = [table1].prodid01)) 

from there have lot of nested statements:

set prodid02 = (case when prodid02 not null prodid02 else  (case when prodid01 not null (select newid [table2] [table2].prodid = [table1].prodid02))  set prodqty02 = (case when prodid02 not null prodqty02 else  (case when prodid01 not null (select newqty [table2] [table2].prodid = [table1].prodid02))  set prodid03 = (case when prodid03 not null prodid03 else  (case when prodid01 not null (case when prodid02 not null (select newid [table2] [table2].prodid = [table1].prodid03)))  set prodqty03 = (case when prodid03 not null prodqty03 else  (case when prodid01 not null (case when prodid02 not null (select newqty [table2] [table2].prodid = [table1].prodid03))) 

etc.

if have on 20 columns, stated, going long query, agree @shnugo might design issue.