I have a scenario like one table contains 10000 rows with one column null. I have other table with different 5 id.

now I want to update table one with those different id like 1st row with 1st id, 2nd row with 2nd id, 3rd row with 3rd id, 4th row with 4th id,,5th row with 5th id, 6th row with 1st id, 7th row with 2nd id and so on.

 table1 sl   value
          ----------
         1    null
         2    null
         3    null
         4    null
         5    null 
         

and so on

table 2 id_name names
            1     ram 
            2     sam
            3     jam
     
     

Now the result should Table 1

  sl   value
      ----------
     1    ram
     2    sam
     3    jam
     4    ram
     5    sam
        

how can we achieve that.

CodePudding user response：

Use the row_number() function to assign each row a row number the corresponds to the id of table2.

declare @table1 table (s1 int, [value] varchar(5));
declare @table2 table (id int, [value] varchar(5));

insert into @table1 (s1)
values
(1),(2),(3),(4),(5),
(6),(7),(8),(9),(10);

insert into @table2 (id, [value])
values
(1,'qwe'),
(2,'asd'),
(3,'zxc'),
(4,'yui'),
(5,'hjk');

with cte as (
    select s1, [value]
        , row_number() over (partition by ((s1-1)/5) 1 order by s1 asc) rn
    from @table1
)
update cte set [value] = (select t2.[value] from @table2 t2 where t2.id = rn);

select * from @table1;

Returns:

Note: If you provide DDL DML as shown here you make it much easier for people to assist.