From Table A to Table B transfer random column values-CodePudding

I have two tables, I wish that Table B would have random Table A values assigned to a specific column.

Table A:

SubscriptionID  | Number | SubStart | SubEnd  | MobPhoneID
1                  321    2013-01     NULL        NULL
2                  123    2013-02    2014-02      NULL
3                  321    2013-03     NULL        NULL
4                  444    2013-04    2013-04      NULL
5                  555    2013-05     NULL        NULL

Table B:

MobPhoneID | Manufacturer
    1          Samsung
    2            LG
    3           Apple
    4            Sony

My goal is:

SubscriptionID  | Number | SubStart | SubEnd  | MobPhoneID
    1              321    2013-01     NULL        3
    2              123    2013-02    2014-02      NULL
    3              321    2013-03     NULL        2
    4              444    2013-04    2013-04      NULL
    5              555    2013-05     NULL        4

Current code that I have:

UPDATE TableA
SET MobPhoneID= t2.MobPhoneID
FROM TableA t1
CROSS APPLY (
    SELECT TOP 1 MobPhoneID
    FROM TableB
    WHERE t1.SubEnd IS NULL
    ORDER BY newid()
    ) t2

The goal is to give the random phone to a person which subscription does not end.

The SQL query above results in the following output:

SubscriptionID  | Number | SubStart | SubEnd  | MobPhoneID
        1           321    2013-01     NULL        2
        2           123    2013-02    2014-02      NULL
        3           321    2013-03     NULL        2
        4           444    2013-04    2013-04      NULL
        5           555    2013-05     NULL        2

It does not add random values, it selects only one random value and applies to all of them.

My wish is so that each person would get a random phone.

I hope my issue makes sense, I cannot seem to figure out what I'm doing wrong.

CodePudding user response：

The t1.SubEnd is Null causes it to pick the same top 1 everytime - if you set it to equal itself, then it reevaluates it.

  UPDATE TableA
    SET MobPhoneID= t2.MobPhoneID
    FROM TableA t1
    CROSS APPLY (
        SELECT TOP 1 MobPhoneID
        FROM TableB
        WHERE t1.SubEnd = t1.SubEnd
        ORDER BY newid()
        ) t2

I've previously used this method and got the expected results.

CodePudding user response：

If a mobile phone can be assigned only once? Then an assigning by random row_number could do it.

;with A as (
  select *, row_number() over (order by SubStart) rn
  from TableA
  where subend is null
)
, B as (
  select *, row_number() over (order by newid()) rn
  from TableB
) 
update A
set MobPhoneID = B.MobPhoneID
from B 
where B.rn = A.rn

select a.*, b.Manufacturer
from TableA a
left join TableB b on b.MobPhoneID = a.MobPhoneID
order by SubscriptionID

SubscriptionID | Number | SubStart | SubEnd  | MobPhoneID | Manufacturer
-------------: | -----: | :------- | :------ | ---------: | :-----------
             1 |    321 | 2013-01  | null    |          3 | Apple       
             2 |    123 | 2013-02  | 2014-02 |       null | null        
             3 |    321 | 2013-03  | null    |          1 | Samsung     
             4 |    444 | 2013-04  | 2013-04 |       null | null        
             5 |    555 | 2013-05  | null    |          2 | LG

Demo on db<>fiddle here