mercredi 5 septembre 2018

Replace existing values in a table with new values from a list Randomly.

Simple table

    Date       |ChannelID 

    01/01/2001 | 1001
    02/01/2001 | 1001
    03/01/2001 | 1001
    04/01/2001 | 1001
    05/01/2001 | 1001
    06/01/2001 | 1001

I need to replace the ChannelID values with either 1001, 2001, or 2002, randomly on each row.

Ive tried doing the following but didnt seem to give me random entries, and missed off the last value for some reason.

    update A

set ChannelID = (
    SELECT TOP 1 b.q
    FROM (
        SELECT 1001 AS q

        UNION ALL

        SELECT 2001

        UNION ALL

        SELECT 2002
        ) b
        CROSS APPLY (
  SELECT TOP 1 b2.q2
    FROM (
        SELECT 1001 AS q2

        UNION ALL

        SELECT 2001

        UNION ALL

        SELECT 2002
        ) b2
WHERE b.q <> b2.q2
ORDER BY newid()
) b2    
    ORDER BY NEWID()
)
From A

Is there an elegant way of achieving this without using temp/variable tables?

Apologies if this is a similar post, but could not find anything without the use of temp tables etc.




Aucun commentaire:

Enregistrer un commentaire