Update Table with Random Record in Update Statment in SQL Server

Update table with random record in update statment in SQL Server?

Ok, I think that this is one of the weirdest query that I've wrote, and I think that this is gonna be terrible slow. But give it a shot:

UPDATE A
SET A.hostessid = B.hostessId
FROM member_info_test A
CROSS APPLY (SELECT TOP 1 hostessId
             FROM hostess_test 
             WHERE A.somecolumn = A.somecolumn
             ORDER BY NEWID()) B

Update table records randomly in SQL Server

I'd like to change the order of the records randomly in the table. Is it possible?

No, this is not possible. SQL tables already represent unordered sets. There is no ordering, although you can define columns that specify an ordering. You can even cluster the rows based on column values, so the data is stored in order. That does not mean it will be retrieved in order.

What you can do is to randomize when you query. In SQL Server:

select t.*
from t
order by newid();

How to update table column with random value from a list of values?

Managed to do it using a CURSOR like this:

DECLARE PostCursor CURSOR 
FOR SELECT PostID FROM dbo.Post WHERE UserID <> 10 AND UserID <> 11 FOR UPDATE
OPEN PostCursor
FETCH NEXT FROM PostCursor 
WHILE @@FETCH_STATUS = 0
BEGIN

     UPDATE dbo.Post 
     SET 
        UserID = (
        SELECT TOP (1) UserID FROM 
        [dbo].[User]
        WHERE UserID IN (1,3,4,7)
        ORDER BY NEWID()
        )

    WHERE CURRENT OF PostCursor
    FETCH NEXT FROM PostCursor
END
CLOSE PostCursor
DEALLOCATE PostCursor

How to update each row in table with random row from another table?

This is a problem of over-active optimizers which decide to run the subquery only once.

One solution is to add a correlation clause to the outer query:

UPDATE Employee
    SET DepartmentId = (SELECT d.Id
                        FROM Department d
                        WHERE employee.id is not null
                        ORDER BY RANDOM() LIMIT 1
                       );

This gets around the optimizer.

Here is a db<>fiddle.

Update SQL table with random value from other table

You can do an order by on a NEWID to get a random number for every row of your update.

UPDATE
    Products
SET
    DefaultImageId =
    (
        SELECT TOP 1
            Id
        FROM
            Images
        WHERE
            Images.ProductId = Products.Id
        ORDER BY
            NEWID()
    )

This has been down marked and comments added indicating it does not solve the problem. I think the confusion has come from where people have not realised the original question requests a random image be selected for each product, hence the where clause with Product Id. Have provided a full script with data set below. It adds five products and three images for each product. Then randomly sets the default image id for each product.

CREATE TABLE Products(Id INT, Name NVARCHAR(100), DefaultImageId INT NULL)

CREATE TABLE Images (Id INT, ProductId INT, Bytes VARBINARY(100))

INSERT INTO Products (Id, NAME, DefaultImageId) VALUES(1, 'A', NULL)
INSERT INTO Products (Id, NAME, DefaultImageId) VALUES(2, 'B', NULL)
INSERT INTO Products (Id, NAME, DefaultImageId) VALUES(3, 'C', NULL)
INSERT INTO Products (Id, NAME, DefaultImageId) VALUES(4, 'D', NULL)
INSERT INTO Products (Id, NAME, DefaultImageId) VALUES(5, 'E', NULL)

INSERT INTO Images (Id, ProductId, Bytes) VALUES(1, 1, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(2, 1, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(3, 1, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(4, 2, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(5, 2, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(6, 2, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(7, 3, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(8, 3, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(9, 3, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(10, 4, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(11, 4, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(12, 4, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(13, 5, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(14, 5, NULL)
INSERT INTO Images (Id, ProductId, Bytes) VALUES(15, 5, NULL)

UPDATE
    Products
SET
    DefaultImageId =
    (
        SELECT TOP 1
            Id
        FROM
            Images
        WHERE
            Images.ProductId = Products.Id
        ORDER BY
            NEWID()
    )

SELECT * FROM Products

How to update all rows in a column with random values?

demo: db<>fiddle

The simple one for your JSON is:

UPDATE 
    data_records dr
SET 
    c2 = jsonb_set(dr.c2, '{variation}', to_jsonb(random()));

---

If you want the second column with the generate_series (for whatever) you will need something to join on the original table. generate_series could give you rows from 1 to 5. So to join on the data_records you would need a 1 to 5 column there too. If this is what is saved in c1 there's no problem. Simply join against c1.

But if not you have to generate it, maybe with a row_number window function which adds the row count as column. Then you are able to join the row count against the generated_series column and you have a row with a random value for each c1 and c2. One of them should be unique. This unique column (c1 in my case) works as the WHERE filter of the UPDATE clause. Of course this could be the c2. But if they are not unique you would end with same random values for same c1/c2 values:

UPDATE 
    data_records dr
SET 
    c2 = jsonb_set(dr.c2, '{variation}', to_jsonb(rand.r))
FROM 
    (SELECT *, row_number() OVER () rn FROM data_records) dr_rn
LEFT JOIN
    (SELECT generate_series(1, 5) gs , random() r) rand 
ON dr_rn.rn = rand.gs

WHERE dr.c1 = dr_rn.c1;

It would be really more simple if you would have an unique id column. But nevertheless I don't see any reasons for making this that complicated.

update table with random values

CREATE TABLE MyTable(
 RowID int IDENTITY(1, 1),   
 Col1 int,
 Col2 int,
 Col3 int,
 Col4 int.
)

DECLARE @RowCount int,
@numberRecords int

select @NumberRecords = count(*) from mytable
SET @RowCount = 1

WHILE @RowCount <= @NumberRecords
BEGIN

UPDATE MyTable
 SET  Col1 = (SELECT TOP 1 RandomColumn1 
         FROM   SampleData 
         ORDER BY NEWID()) 

 WHERE RowID = @RowCount

 SET @RowCount = @RowCount + 1
END

Hope that is some help.. an expansion of my answer.. as you can see I create your table but add a row-id column that increments.. i then create a loop that runs the update statment on a row per row basis.

Very similar to how a cursor would function but hopefully quicker.

---

Related Topics