16

On Microsoft SQL Server 2008, I have a table with Products:

Id | Name | DefaultImageId

And one with Images:

Id | ProductId | Bytes

I want to run an Update statement, that updates the DefaultImageId on all records in the Products table with a random Id from the Images table that is related to the Product via the ProductId column.

Can anyone help out? Should be simple for any SQL Champ (Which is obviously not me)..

Improve this question

6 Answers 6

Reset to default
23

Addressing @philreed's issue with the selected answer:

Is there a way to assign each row being updated with a different value randomly chosen from the source table?

UPDATE Products SET DefaultImageId = t2.Id FROM Products t1 CROSS APPLY ( SELECT TOP 1 Id FROM Images WHERE t1.Id = t1.Id ORDER BY newid() ) t2
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

This solved my issue with getting the same random number on each row. The "where t1.Id = t1.Id" is needed to make it work, I initially didn't notice it and though the cross apply was the only difference between the accepted answer and this one.
For the record (no pun intended), this is not very performant if used against a large-ish dataset with (I'm updating a phone number from a list of 10000 random phone numbers to scramble a table of 300k members and am currently at 33min and wondering how much longer...)
maybe you could post with details?
@KrishnanVenkiteswaran This is not the correct answer as it randomly assigns any Image.Id to the Product.DefaultImageId table, not only those that are related to the Product.
I was looking for a random update of the first table with values from the 2nd table and Cross Apply was what worked for me, OP's problem is a different one though.
12

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
Improve this answer

4 Comments

This solution appears to assign the same randomly chosen value to each row being updated. Is there a way to assign each row being updated with a different value randomly chosen from the source table (Images in your example)?
Please see if the solution just posted solves this, @philreed
@KrishnanVenkiteswaran in what way isn't it working? I have updated the answer with create table and data inserts to prove it provides the solution.
Yes, it does work, apologies, I didn't read the question, I was looking at just assigning random values from table to another.
5

Another possible solution

UPDATE Products SET DefaultImageId = ( SELECT TOP 1 Id FROM Images ORDER BY NEWID(), Products.Id )
Improve this answer

1 Comment

Great this will not save null values if image table length less than product
3

Try this one (on AdventureWorks):

It updates all rows of the person table with a random name from the product table.

/*select rows from both tables in random order and match records by row number */ update person.person set FirstName = otherTable.Name from (select BusinessEntityID, row_number() over (order by newid()) as row_num from person.person) tableWithRownum ,(select ProductId, Name, row_number() over (order by newid()) as row_num from production.product) as otherTable where person.person.BusinessEntityID=tableWithRownum.BusinessEntityID and (tableWithRownum.row_num%500)=otherTable.row_num /*match by row number;*/ /* optional: correct for different table sizes with modulo operation*/

Notes:

  • The new values are not sampled independently for each row.
  • Since the random order is determined only once, all entries occur at roughly the same rate (depending on table sizes: If both tables have the same number of rows, each entry should occur exactly once).
  • This behaviour might not fit every use case, but it should help performance...
Improve this answer

2 Comments

With my testing this has proved to work faster than the cross apply solution.Much much faster. Down from 42 mins to 20 seconds on a slow Azure SQL database. Thank you.
Although this is more complex than other answers, it is more versatile and faster. It should be the accepted answer.
1

Check this out.

Update Products Set DefaultImageId = ( SELECT top 1 Id From Images Where 1=1 and Products.Id = Images.ProductId ORDER BY NEWID() )
Improve this answer

1 Comment

Msg 147, Level 15, State 1, Line 2 An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
-1 
declare @carg_id as int DECLARE cursor_cargo CURSOR FOR SELECT carg_id FROM cargo OPEN cursor_cargo FETCH NEXT FROM cursor_cargo INTO @carg_id WHILE @@FETCH_STATUS =0 BEGIN update cargo set ciud_id = ( select top 1 ciud_id from ciudad inner join Pais on Pais.pais_id = ciudad.pais_id where ciudad.pais_id is not null and empr_id is null and pais_status =1 order by NEWID()) WHERE CARG_ID = @CARG_ID FETCH NEXT FROM cursor_cargo INTO @carg_id END CLOSE cursor_cargo DEALLOCATE cursor_cargo
Improve this answer

1 Comment

Cursors are generally a bad idea, and the columns you entered don't match the OP's

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.