What's the best way to do "Foreach Update" in T-SQL?
I am using SQL Server 2008 DB via SSMS2014
I am trying to update a table with the names of the first and last values stored in the "randomnames" table which contains:
Id (Identity)
firstname
lastname
My current code:
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 first and last name. I would like all entries to have different random names and last names, i.e. I need to see the above for each entry, not all entries.
I'm probably missing something simple? I need to do this through some SQL in SSMS.
Tips are much appreciated.
Thank.
EDIT
create table testtable
(
id INT IDENTITY(1,1) PRIMARY KEY,
firstname VARCHAR(100),
lastname VARCHAR(100)
)
create table randomtab (name VARCHAR (100), name 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
source to share
You can also use something like this.
Setting 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
source to share
I think that when you order your random number, it should work: change your code to something like 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 it 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:
source to share
Have you considered passing a column value ID
to a random number generation function? You might 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 ensures that every row in the TestTable has values generated based on a specific parameter.
source to share