Michel Michel - 5 months ago 35
SQL Question

Update random top 50 rows

I want to update 50 rows in my table to the current date.
I know how to select 50 random results but I don't know how to update them.

This is what I have for the Select code:

SELECT TOP 50[ID]
,[Message]
,[Date]
,[Type]
,[Username]
FROM [database].[dbo].[dbTable]
ORDER BY NEWID()


If i'm correct, this will get the 50 random rows.

first I just updated the first 50 but I want to get some random values out of my database.

update top(50) dbTable
set date=getdate()


I don't want to mess up the database because I don't have access to a back up today.

If there is something unclear or anything please ask me!

Answer

Try this

UPDATE [database].[dbo].[dbTable] Set [Date] = GetDate() where [ID] IN
(
    SELECT TOP 50 [ID] FROM [database].[dbo].[dbTable] ORDER BY NEWID()
)

Or else:

WITH    q AS
        (
         SELECT TOP 50[ID]
              ,[Message]
              ,[Date]
              ,[Type]
              ,[Username]
         FROM [database].[dbo].[dbTable]
         ORDER BY NEWID()
        )
UPDATE  q
SET     [Date] = GetDate()