Geek Answers Handbook

MS SQL Server: update with fewer rows in the source table

What is the best way to update Table1with all values ​​from Table2if it Table2has fewer rows than Table1? This means that it Table2does not have a key that can be attached to Table1for updating.

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 it 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
+4
source share
2 answers

An interesting problem. This is the first solution for MySQL (I initially read the question about that database). After that, the solution is for SQL Server.

join. , id . , :

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;

table1 , . :

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:

SQL Server. :

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);

, id . , .

+4

.

DECLARE @Table1  AS TABLE
(
    ID INT,
    Value INT
)

DECLARE @Table2 AS  TABLE
(
    Value INT
)

INSERT INTO @Table1
SELECT 1, NULL UNION
SELECT 2, NULL UNION
SELECT 3, NULL UNION
SELECT 4, NULL UNION
SELECT 5, NULL UNION
SELECT 6, NULL UNION
SELECT 7, NULL 


INSERT INTO @Table2
SELECT 4 UNION
SELECT 6 UNION
SELECT 8 

DECLARE @nCOUNT as INT

SET @nCOUNT = (SELECT COUNT(*) FROM @Table2)

UPDATE TB1 SET TB1.Value = TB2.Value FROM @Table1 AS TB1
INNER JOIN
(SELECT T1.ID, T2.Value FROM 
(SELECT *, CASE WHEN (ROW_NUMBER() OVER(ORDER BY ID) % @nCOUNT) = 0
                THEN @nCOUNT
                ELSE (ROW_NUMBER() OVER(ORDER BY ID) % @nCOUNT)
            END AS ROID
FROM @Table1) AS T1

LEFT JOIN (SELECT VALUE, ROW_NUMBER() OVER(ORDER BY Value) AS ID FROM @Table2) AS T2 ON T2.ID = T1.ROID) AS TB2
ON TB1.ID = TB2.ID

SELECT * FROM @Table1
0

More articles:


All Articles