Menu
I have the follow T-SQL to update a table with test data:
UPDATE
SomeTable
SET
Created = GETDATE ( ) - CAST ( RAND ( ) * 365 AS int ) ,
LastUpdated = GETDATE ( ) - CAST ( RAND ( ) * 365 AS int )
I want it to pick random daes in the past year, unfortunately it uses the same date for every row. what is the best way to get it to be random every row it updates?
978
Syntax: UPDATE table_name SET column_name1 = new_value1, column_name2 = new_value2 ---- WHERE condition; Here table_name is the name of the table, column_name is the column whose value you want to update, new_value is the updated value, WHERE is used to filter for specific data.
The LIMIT clause places a limit on the number of rows that can be updated. For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions.
There are a couple of ways to do it. INSERT INTO students (id, score1, score2) VALUES (1, 5, 8), (2, 10, 8), (3, 8, 3), (4, 10, 7) ON DUPLICATE KEY UPDATE score1 = VALUES(score1), score2 = VALUES(score2);
An UPDATE statement affects rows that already exist in the table. To add a new row to a table, you'd use an INSERT statement. (Note that the UPDATE statement in the question will update every row in the table; there isn't any WHERE clause.)
Use RAND(CHECKSUM(NEWID()))
In your case, you could modulo the checkum because CHECKSUM(NEWID()) is already random.
CHECKSUM(NEWID()) % 365 if you only want days from the past year use this (based on @gbn's answer):
select GETDATE ( ) - ABS( CHECKSUM(NEWID()) % 365 ) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
