what i have done is to  convert the records in userimport from columns as rows first.
ie (udf1, udf2, ...) is converted as
userid,fieldno,value
1 , 1 , New1
1 , 2 , New2
1 , 3 , New3
1 , 4 , New4
1 , 5 , New5

So the result of that step would be how we want the final output to look like.

This is followed by a update using join with the result set in step 1 with the udf table on the keys userid,fieldno.
(You could also accomplish the same using MERGE statement as well)


create table users
(
userid int,
firstname varchar(25),
lastname varchar(25),
middlename varchar(25)
);

insert into users values (1,'some','dude','big');

create table udf(userid int,fieldno int, value varchar(25));

insert into udf values (1,1,'123456');
insert into udf values (1,2,'text1');

create table userimport
(
userid int
,firstname varchar(25)
,lastname varchar(25)
,middlename varchar(25)
,udf1 varchar(25)
,udf2 varchar(25)
,udf3 varchar(25)
,udf4 varchar(25)
,udf5 varchar(25)
);

insert into userimport values(1,'some','dude','big','New1','New2','New3','New4','New5');

update a2
    set a2.value=b2.value1
   from udf a2
   join (select a.userid
               ,b.rnk as fieldno
               ,case when b.rnk=1 then a.udf1
                    when b.rnk=2 then a.udf2
                    when b.rnk=3 then a.udf3
                    when b.rnk=4 then a.udf4
                    when b.rnk=5 then a.udf5
                end as Value1 
            from userimport a
            join (select top 5 row_number() over(order by (select null)) as rnk
                    from information_schema.tables
                 )b
              on 1=1
         )b2
       on a2.userid=b2.userid
      and a2.fieldno=b2.fieldno; 
  
select *
  from udf;