UPDATE random row from another table SQL Server 2014

Question

I tried to do an UPDATE statement with a random row from another table. I know this question has been asked before (here), but it doesn't seem to work for me.

I should update each row with a different value from the other table. In my case it only gets one random row from a table and puts that in every row.

UPDATE dbo.TABLE_CHARGE SET COLRW_STREET = (SELECT TOP 1 COLRW_STREET FROM CHIEF_PreProduction.dbo.TABLE_FAKESTREET ORDER BY ABS(CHECKSUM(NewId())%250))

Thanks in advance!

Thanks for your response! I edited my question. The part that does not work is the part where it updates random values. It does take a random value, but only one. After that, the random values gets inserted in every single place. A URL with about the same question: stackoverflow.com/questions/5003028/… — user3208216
– user3208216, Commented Sep 19, 2014 at 9:14
the difference to the link you provided is to update from another table and this works fine in SQLServer 2008 and SQLServer 2012 sqlfiddle.com/#!3/ab537/1 — bummi
– bummi, Commented Sep 19, 2014 at 10:06
Thanks for your response. I have SQL Server 2012 AND SQL Server 2012, they both don't work on my PC. — user3208216
– user3208216, Commented Sep 19, 2014 at 10:21
I think it will be more helpful if you could comment on responses with details of what exactly does not work, otherwise it is a confusing message. Also you can use mentions - @user3208216 — Bulat
– Bulat, Commented Sep 19, 2014 at 10:24

Bulat · Accepted Answer · 2014-09-19 10:55:31Z

I took a liberty to assume that you have ID field in your TABLE_CHARGE table. This is probably not the most efficient way, but seems to work:

WITH random_values as ( SELECT t.id, t.COLRW_STREET, t.random_street FROM ( SELECT c.id, c.COLRW_STREET, f.COLRW_STREET as random_street, ROW_NUMBER() OVER (partition by c.id ORDER BY ABS(CHECKSUM(NewId())%250)) rn FROM table_charge c, TABLE_FAKESTREET f) t WHERE t.rn = 1 ) UPDATE random_values SET COLRW_STREET = random_street;

SQL Fiddle demo

Your original code did not work because when yo do ... SET x = (SELECT TOP 1 ..) database does OUTER JOIN of your target table with one TOP row, which means that one single row is applied to all rows in your target table. Hence you have same value in all rows.

Following query demonstrates what is happening in the UPDATE:

SELECT * FROM TABLE_CHARGE tc, (SELECT TOP 1 COLRW_STREET as random_street FROM TABLE_FAKESTREET ORDER BY ABS(CHECKSUM(NewId())%250)) t

My solution gets all fake records ordered randomly for each record in target table and only selects the first one per ID.

I do not know what this does, to be honest. I am not very familiar with CTEs. It is working indeed, so thanks!
I have updated my answer, hope it will help to understand my solution

Collectives™ on Stack Overflow

UPDATE random row from another table SQL Server 2014

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related