Search code examples
sqlsql-servert-sql

Replace null values with values random values from a table

The results from a SQL query produces the following table:

|SEDOL |   ISIN      | Cash |
=============================
|ZZZ0072|GB00B7JYLW09| null |
-----------------------------
|CASH   |GB00B7JYLW09| null |
-----------------------------
|ZZZ0072|GB00B7JYLW09| null |
-----------------------------
|ZZZ0009|GB00B7JYLW09| null |
-----------------------------

I would like help with a query that will replace the null values in the Cash field with values (any value) from the table testtable with field 'IsCash' and to return the following table:

=============================
|SEDOL |   ISIN      | Cash |
=============================
|ZZZ0072|GB00B7JYLW09|150146|
-----------------------------
|CASH   |GB00B7JYLW09| 182  |
-----------------------------
|ZZZ0072|GB00B7JYLW09| 1190 |
-----------------------------
|ZZZ0009|GB00B7JYLW09| 2000 |
-----------------------------

The testtable is:

|    IsCash  |
==============
|     36     |
--------------
|   150146   |
--------------
|    182     |
--------------
|   2000     |
--------------
|   200952   |
--------------
|   200000   |
--------------
|   350000   |
--------------
|   150000   |
--------------
|    1190    |
--------------

I think I would need to apply the ISNULL statement to the 'Cash' field, but I'm not sure on the clause used to issue the replace statement from the testtable with the IsCash field.

Results could easily appear as follows, because I just want any random value from the testtable from field IsCash to appear in the Cash field as follows

| SEDOL  |  ISIN      |  Cash |
===============================
|ZZZ0072 |GB00B7JYLW09| 200952|
-------------------------------
|  CASH  |GB00B7JYLW09| 200000|
-------------------------------
|ZZZ0072 |GB00B7JYLW09| 150000|
-------------------------------
|ZZZ0009 |GB00B7JYLW09| 1190  |
-------------------------------
Solution

  • Please try the following solution.

    SQL

    -- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, SEDOL  VARCHAR(512), ISIN  VARCHAR(512), Cash VARCHAR(512));
INSERT INTO @tbl (SEDOL, ISIN, Cash) VALUES 
('ZZZ0072', 'GB00B7JYLW09', NULL),
('CASH', 'GB00B7JYLW09', NULL),
('ZZZ0072', 'GB00B7JYLW09', NULL),
('ZZZ0009', 'GB00B7JYLW09', NULL),
('Test', 'Test', 'Test');

DECLARE @cash TABLE (IsCash varchar(512));
INSERT INTO @cash (IsCash) VALUES
('36'),     
('150146'),   
('182'),     
('2000'),     
('200952'),   
('200000'),   
('350000'),   
('150000'),   
('1190'); 
-- DDL and sample data population, end

;WITH rs AS
(
   SELECT *
      , seq = ROW_NUMBER() OVER (ORDER BY NEWID())
   FROM @cash
)
UPDATE t
SET Cash = rs.IsCash
FROM @tbl AS t INNER JOIN rs ON rs.seq = t.ID;

-- test
SELECT * FROM @tbl;

    Output

    ID SEDOL ISIN Cash
    1 ZZZ0072 GB00B7JYLW09 150000
    2 CASH GB00B7JYLW09 350000
    3 ZZZ0072 GB00B7JYLW09 2000
    4 ZZZ0009 GB00B7JYLW09 1190
    5 Test Test 36