SQL query to update a column from another column randomly without any condition-CodePudding

I want to update the table A column 'postcode' by randomly select an 'Element' from the table 'B' then add a space, a random number and 2 random letters.

For Example:

Table A

ID   postcode
1     ABC 1GH
2     CDE 2MH
3     SAD 6GK

Table B

ID  ELEMENT
1   FJL
2   WHO
3   HPS

Expected Output in TABLE A ( RANDOM Element from Table B followed by space followed by random single digit number followed by 2 alphabets)

ID Postcode
1  WHO 8GH
2  HPS 2UI
3  FJL 4YT

CodePudding user response：

You can use below

select ELEMENT   ' '   convert(varchar(1),ABS(CHECKSUM(NEWID()) % 2))   char(cast((90 - 65 )*rand()   65 as integer))  char(cast((90 - 65 )*rand()   65 as integer))
FROM B

CodePudding user response：

First of all we must be sure that cardinality of TableB MUST BE greater than or equal to cardinality of TableA.

We need to add a random info to TableB that will be used both to obfuscate ELEMENT and to shuffle the order. For this purpose we will add a UNIQUEIDENTIFIER column with random values from NEWID() function.

To shuffle the order we will number TableA.ID and the new random column in TableB adding a sort of interface between them.

To obfuscate ELEMENT we will strip portions of our random info.

;with
TblB as (
    SELECT *, convert(varbinary(16), NEWID(), 1) rndord
    FROM TableB
),
TableAx as (
    SELECT *, ROW_NUMBER() over (order by id) idx
    FROM TableA
),
TableBx as (
    SELECT *, ROW_NUMBER() over (order by rndord) idx
    FROM TblB
)
select a.id, ELEMENT   ' '  
    cast((abs(convert(bigint, rndord)) % 9) as char(1))
      char(65   abs(convert(bigint, substring(rndord, 9, 4))) % (90-65)) 
      char(65   abs(convert(bigint, substring(rndord, 13, 4))) % (90-65)) postcode
from TableAx a 
left join TableBx b on a.idx = b.idx

This shoul do the trick