Replace null values with values random values from a table

Question

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

Answer 1

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