TSQL overwrite whole table column with the value of one row in the column (See related posts)

You want to remove some sensitive data from a column in a table in a test database. Just to make things hard the secret data within the column are massive encoded blobs. Large blobs dont work in subqueries nor the standard datbase tools - you would have to extracting it to disk. So you are looking for an 'update from select' query do solve the problem.

You have added one new row into the table that has test data in the sensitive column. You now want a query to write the sensitive column in this new row over the rest of the column in the table. The following code does the job:

update SECRET_TABLE set SECRET_COLUMN = SOURCE_TABLE.NOT_SECRET from SECRET_TABLE st1 inner join (
select st2.[ID] as [ignore], st3.[ID], st2.[SECRET_COLUMN] as [NOT_SECRET] from SECRET_TABLE st2 cross join SECRET_TABLE st3 where st2.[ID] = 'not_secret_row_id'
) as SOURCE_TABLE on SOURCE_TABLE.[ID] = st1.[ID]

it is a bit slow due to the cross join but as it was an infrequent task it was not a problem for me. any hints on doing it without the cross join?