mister.magoo (4/19/2014)
Like I said, I couldn't guarantee it, although on the test data you supplied, it worked for me where the other methods failed.You definitely used an outer apply, and updated both columns from the outer apply?
YES, BUT:
Translating your solution to my situation I introduced an error. Used an alias two times, so the inner query did not reverence the target table.
Query is now running in corrected format. (Over 2 minutes now).
(over 4 minutes now, this is with the target over over 700000 and the values over 4000 rows).
I'll be back in 10 minutes if the scripts hasn't finished then I'll abort it.
Edit :
space for the result.
Made some more mistakes, but I think I have the results now.
And: ((Msg 9002, Level 17, State 4, Line 2
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases))
3:00 minutes running time and as far as I can see the result is correct.
Now I first have to rebuild my test dataset and do the run again.
I have to find a solution to use an extra column from the target table. Although this column is not changed I need a columnname for that. Could pick a random one from the information_schema.
At the moment I am coming up with an alternate solution, but that takes some development, so that is not ready yet.
(Involving a large table containing the random values).
Thanks,
Ben