2

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!

Community
  • 1
  • 1
user3208216
  • 84
  • 1
  • 8
  • 1
    what exactly does not work and where was it asked before? – Bulat Sep 19 '14 at 09:00
  • 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: http://goo.gl/pyHuhG – user3208216 Sep 19 '14 at 09:14
  • the difference to the link you provided is to update from another table and this works fine in SQLServer 2008 and SQLServer 2012 http://sqlfiddle.com/#!3/ab537/1 – bummi Sep 19 '14 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 Sep 19 '14 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 Sep 19 '14 at 10:24
  • @Bulat your code worked. I am still confused why my previous code doesn't work. I takes 1 random value from the table and inserts it on every row. – user3208216 Sep 19 '14 at 10:33

1 Answers1

3

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.

Bulat
  • 6,869
  • 1
  • 29
  • 52