What is the best way to do a "Foreach Update" in T-SQL?

Question

I am using a SQL Server 2008 DB via SSMS2014

I am trying to update a table with test firstname and lastname values that are stored in a "randomnames" table which contains:

Id (Identity)
firstname
lastname

My current code is :

update TestTable
set 
FirstName = (select firstname from randomnames where ID = ABS(Checksum(NewID()) % 4) + 1),
LastName = (select lastname from randomnames where ID = ABS(Checksum(NewID()) % 4) + 1)
;

However the above will set all records to the "same" random firstname and lastname. I would like all records to have different random firstnames and lastnames ie I need the above to run for each record rather than for all records.

I am probably missing something simple? I need to do this via some SQL in SSMS.

Advice hugely appreciated.

Thanks.

EDIT

create table testtable
(
id INT IDENTITY(1,1) PRIMARY KEY,
firstname VARCHAR(100),
lastname VARCHAR(100)
)

create table randomtab ( firstname VARCHAR(100), lastname VARCHAR(100) )

insert into testtable(firstname,lastname) 
values (NULL,NULL),(NULL,NULL),(NULL,NULL),(NULL,NULL),(NULL,NULL),      
(NULL,NULL),(NULL,NULL)
insert into randomtab(firstname,lastname)
values ('first1','last1'),('first2','last2'),('first3','last3'),   
('first4','last4'),('first5','last5'),('first6','last6'),('first7','last7')


update testtable
set firstname = (select top 1 firstname from randomtab order by NEWID()),
lastname = (select top 1 lastname from randomtab order by NEWID())

SELECT * FROM testtable

update testtable
set firstname = (select top 1 firstname from randomtab order by NEWID()),
lastname = (select top 1 lastname from randomtab order by NEWID())

SELECT * FROM testtable

maybe this link help someone, upadate from select: https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server — yu yang Jian, Jan 10 '18 at 06:14

score 3 · Accepted Answer · answered Jul 06 '15 at 13:14

You can also use Something like this.

SQL Fiddle

Setup and Data

CREATE TABLE TestTable
(
    Id INT IDENTITY(1,1),
    firstname VARCHAR(20) DEFAULT(''),
    lastname VARCHAR(20) DEFAULT('')
);


CREATE TABLE randomnames
(
    Id INT IDENTITY(1,1),
    firstname VARCHAR(20),
    lastname  VARCHAR(20)
);

 insert into TestTable DEFAULT VALUES;
 insert into TestTable DEFAULT VALUES;
 insert into TestTable DEFAULT VALUES;
 insert into TestTable DEFAULT VALUES;
 insert into TestTable DEFAULT VALUES;
 insert into randomnames VALUES('F1','L1'),('F2','L2'),('F3','L3'),('F4','L4');

Query

;WITH CTE as
(
    SELECT *,ABS(Checksum(NewID()) % 4) + 1 as fnameid,ABS(Checksum(NewID()) % 4) + 1 as lnameid
    FROM TestTable
)
update CTE
set 
FirstName = (select firstname from randomnames where ID =fnameid),
LastName = (select lastname from randomnames where ID =lnameid);

Output

Id  firstname   lastname
1   F4  L1
2   F3  L3
3   F4  L2
4   F3  L2
5   F2  L1

I tried the above code and found duplicate FirstName and LastName in result. Duplicate allowed? — Jesuraja, Jul 07 '15 at 04:46

CeOnSql · Answer 2 · 2015-07-06T13:14:11.137

2

I think when you order by your random number it should work: change your code to something likes this:

update TestTable
set 
FirstName = (select top 1 firstname from randomnames order by NEWID()),
LastName = (select top1 lastname from randomnames order by NEWID())
;

EDIT: To show you how this works on my localhost - this is my test batch:

declare @testtable table 
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    firstname VARCHAR(100),
    lastname VARCHAR(100)
)

declare @randomtab table 
(
    firstname VARCHAR(100),
    lastname VARCHAR(100)
)

insert into @testtable(firstname,lastname) 
values (NULL,NULL),(NULL,NULL),(NULL,NULL),(NULL,NULL),(NULL,NULL),(NULL,NULL),(NULL,NULL)
insert into @randomtab(firstname,lastname)
values ('first1','last1'),('first2','last2'),('first3','last3'),('first4','last4'),('first5','last5'),('first6','last6'),('first7','last7')


update @testtable
set firstname = (select top 1 firstname from @randomtab order by NEWID()),
    lastname = (select top 1 lastname from @randomtab order by NEWID())

SELECT * FROM @testtable

update @testtable
set firstname = (select top 1 firstname from @randomtab order by NEWID()),
    lastname = (select top 1 lastname from @randomtab order by NEWID())

SELECT * FROM @testtable

OUTPUT:

output

edited Jul 06 '15 at 13:14

answered Jul 06 '15 at 13:07

CeOnSql

2,615
1
16
38

1

@Ullas That is not true – Radu Gheorghiu Jul 06 '15 at 13:10
@SamJolly did not work means you got no random values? as you see in my test batch it works fine :/ (on SQL Server 2014 express edition x64 - but i dont think ss-version matters here - correct me if i am wrong) – CeOnSql Jul 06 '15 at 13:21
Thanks for this. I did try your code and yes when "@" vars are used then it does work, but interestingly if physical tables are used then it does not work. I have converted your code to "physical" tables which does not work ie all records are updated with the same value. Yes, with variables, each record is different. – SamJolly Jul 06 '15 at 13:46
Look at 4th and 6th row of first result, you can find the duplicate records... Is it correct? – Jesuraja Jul 07 '15 at 10:54

score 2 · Answer 3 · answered Jul 06 '15 at 13:16

2

Have you considered passing the value of the ID column in to some random number generating function? You could have something like

update tt
   set FirstName = (select top 1 firstname from randomnames r where r.Id = MyRandomNumberFunction(tt.Id)),
       LastName  = (select top 1 lastname from randomnames r where r.Id = MyRandomNumberFunction(tt.Id)),
  from TestTable tt;

This would ensure each row in TestTable will have values generated based on a specific parameter.

answered Jul 06 '15 at 13:16

amcdermott

1,565
15
23

Thanks for this, but will all of the records of "tt" not be updated to the same "random" value since "select" will be run once. – SamJolly Jul 06 '15 at 13:18
1

I don't think so - because a different seed value (i.e the `TestTable.ID` value) would be passed to your random function for each row. Your function would accept the integer and return a random value between 1 and the max ID in your random names table`- this is then used to determine what record from randomnames to return. – amcdermott Jul 06 '15 at 13:23

What is the best way to do a "Foreach Update" in T-SQL?

3 Answers3