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