MS SQL Server : update with less amount of rows in source table

sql, sql-server, sql-server-2012

Solution

Interesting problem. This first solution is for MySQL (I originally read the question as being about that database). After this solution is the one for SQL Server.

You need to generate a `join` key. Let me assume that `id` is really sequential. Then you can use modulo arithmetic to do the match:

update table1 t1
       (select (@rn := @rn + 1) as seqnum, value
        from table2 cross join
             (select @rn := -1) vars
       ) t2 cross join
       (select count(*) as cnt from table2) cnt
       on mod((t1.id - 1), cnt.cnt) = t2.seqnum
    set t1.value = t2.value;

If the id in `table1` is not sequential, you can use a variable for that as well. It just further complicates the query:

update table1 t1 join
       (select @rn1 := @rn + 1) as seqnum, id
        from table1 t1 cross join
             (select @rn1 := 0) vars
        order by id
       ) t1s
       on t1.id =  t1s.id join
       (select (@rn := @rn + 1) as seqnum, value
        from table2 cross join
             (select @rn := -1) vars
       ) t2 cross join
       (select count(*) as cnt from table2) cnt
       on mod((t1s.seqnum - 1), cnt.cnt) = t2.seqnum
    set t1.value = t2.value;

EDIT:

You can readily do the same thing in SQL Server. It is actually easier:

update table1 t1
    set t1.value = t2.value;
    from table1 t1 join
         (select t2.*, count(*) over () as cnt
          from table2 t2
         ) t2
         on (t1.id - 1) % t2.cnt = (t2.id - 1);

This formulation depends on the `id`s being sequential with no gaps. It is easy enough to loosen this restriction, but the query gets a wee bit more complicated.

Problem

What is the best way to update `Table1` with all values from `Table2` if `Table2` has less rows than `Table1`? This considering that `Table2` has no key that can be joined to `Table1` for update. ``` TABLE1 TABLE2 RESULT TABLE1 id value value id value ---------------------------------------------------- 1 NULL 4 1 4 2 NULL 6 2 6 3 NULL 8 3 8 4 NULL 4 4 5 NULL 5 6 6 NULL 6 8 7 NULL 7 4 ``` Hope I make sense. Thanks in advance. EDIT: Pardon, did not specify its Microsoft SQL Server 2012. :/ EXAMPLE for SOLUTION: ``` DECLARE @t1 TABLE(id int, avalue int) DECLARE @t2 TABLE(id INT, avalue int) -- Generate 20 rows in @t1 table INSERT INTO @t1 (id) SELECT Number FROM dbo.Numbers WHERE Number BETWEEN 1 AND 20 -- Generate 5 rows and value @t2 table INSERT INTO @t2 (id,avalue) SELECT Number, Number FROM dbo.Numbers WHERE Number BETWEEN 1 AND 5 -- The goal is to take all rows from @t2 -- and repeatively insert them in order into @t1 UPDATE t1 SET t1.avalue = t2.avalue FROM @t1 t1 JOIN ( SELECT t2.*, COUNT(*) OVER () AS cnt FROM @t2 t2 ) AS t2 ON (t1.id - 1) % t2.cnt = t2.id - 1; SELECT * FROM @t1 ```

Original source