CODEWITHSUNDEEP
sqlsql-server
john
john

Reputation: 129

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

Note : Table B size is smaller than Table A. Null is not allowed while updating data in table A.

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
4     GOD 2UO

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[7:40 PM] Prasanna, Prashanth (Proagrica-CON)

My present query :

update tableA
set postcode = A.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))
from tableB b
inner join
(SELECT *, convert(varbinary(16), NEWID(), 1) rndord,ROW_NUMBER() OVER(ORDER BY NEWID()) AS SNO
FROM tableB) AS original on original.BrickId = b.brickid
inner JOIN
(SELECT id,ROW_NUMBER() OVER(ORDER BY NEWID()) AS SNO
, element
FROM tableB) AS A ON original.SNO =A.SNO

This updates only few columns and other columns are untouched.

Upvotes: 1

Views: 447

Answers (2)

MtwStark
MtwStark

Reputation: 4058

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

After the EDIT we assume that cardinality of TableB can also be smaller than cardinality of TableA, so we 1st need to multiply TableB rows enough to be more then TableA rows (tables Cnt and numbers) and then use the numbers table in TblB to multiply the rows.

In this mode, ELEMENT could be repeated across rows, but random part will be different.

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
Cnt as (
    select cast(ceiling(1.0*CntA.cnt/CntB.cnt) as int) Btimes
    from 
        (select count(*) cnt from TableA) CntA, 
        (select count(*) cnt from TableB) CntB
),
numbers as (
        select top((select Btimes from cnt)) ROW_NUMBER() over (order by object_id) n
        from sys.objects
),
TblB as (
    SELECT *, convert(varbinary(16), NEWID(), 1) rndord
    FROM TableB, numbers
),
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 should do the trick

Upvotes: 1

huMpty duMpty
huMpty duMpty

Reputation: 14470

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

Upvotes: 1

Related Questions