CREATE TABLE Products(Id INT, Name CHAR(100), DefaultImageId INT NULL); INSERT INTO Products (Id, Name, DefaultImageId) VALUES(1, 'A', NULL); INSERT INTO Products (Id, Name, DefaultImageId) VALUES(2, 'A', NULL); INSERT INTO Products (Id, Name, DefaultImageId) VALUES(3, 'A', NULL); INSERT INTO Products (Id, Name, DefaultImageId) VALUES(4, 'A', NULL); INSERT INTO Products (Id, Name, DefaultImageId) VALUES(5, 'A', NULL); INSERT INTO Products (Id, Name, DefaultImageId) VALUES(1, 'B', NULL); INSERT INTO Products (Id, Name, DefaultImageId) VALUES(2, 'B', NULL); INSERT INTO Products (Id, Name, DefaultImageId) VALUES(3, 'B', NULL);
In general, I would update a table randomly like the following scripts.
update a set DefaultImageId=1 from Products as a where name = 'A' and id in ( select top 2 id from Products as b where a.name = b.name order by newid() )
However, I get some issue. It would update more/less then 2 rows. I try execute the following scripts many times for debug.
The results are not always only two records. If I remove the order by newid()
, the number of output result will be fine. It seems like the problem in newid()
. How can I solve this problem? Thanks
select * from Products as a where name = 'A' and id in ( select top 2 id from Products as b where a.name = b.name order by newid() )
Advertisement
Answer
You can try this:
UPDATE U SET DefaultImageId = 1 FROM( SELECT TOP 2 * FROM dbo.Products WHERE Name = 'A' ORDER BY NEWID() ) AS U
However if you filter out the DefaultImageId=1
in inner query, it would be even better too. You should note that in this case, the inner query might produce less than 2 records.
UPDATE U SET DefaultImageId = 1 FROM( SELECT TOP 2 * FROM dbo.Products WHERE Name = 'A' AND DefaultImageId <> 1 ORDER BY NEWID() ) AS U