StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUpdate specific columns in a table iteratively (Do a bulk update)
primarykey
Id
17532001
data
AcceptedAnswerId
0
AnswerCount
1
ClosedDate
CommentCount
4
CommunityOwnedDate
CreationDate
2013-07-08T16:51:13.300
FavoriteCount
0
LastActivityDate
2013-07-08T17:45:28.687
LastEditDate
LastEditorUserId
0
OwnerUserId
2471553
ParentId
0
PostTypeId
1
Score
0
ViewCount
1086
LastEditorDisplayName
text
Body
My Table Schema is as follows: Gender: char(1), not null Last Name: varchar(25), null First Name: varhcar(35), not null The data in the table looks like: <pre><code> Gender | Last Name | First Name | M Doe John F Marie Jane M Jones Jameson F Simpson Alice </code></pre> I now am trying to update all the names in the table from the names present in the txt file. My Query is as follows: <pre><code>-- Sort out the Forenames we'll be using for the data, we make a #Name2 table because I have yet to figure our -- inserting specific columns using BULK INSERT and without using a format file. CREATE TABLE #Name (Name VARCHAR(50)) CREATE TABLE #ForeNames (FirstName VARCHAR(50), Gender VARCHAR(1)) -- Move data in the #Name2 table BULK INSERT #Name FROM "c:\girlsforenames.txt" WITH (ROWTERMINATOR='\n') -- Now move it to the forename table and add the gender INSERT INTO #ForeNames SELECT [Name], 'F' FROM #Name -- Delete the names from temporary table TRUNCATE TABLE #Name -- Same for the boys BULK INSERT #Name FROM "c:\boysforenames.txt" WITH (ROWTERMINATOR='\n') INSERT INTO #ForeNames SELECT [Name], 'M' FROM #Name -- Now do the surnames TRUNCATE TABLE #Name BULK INSERT #Name FROM "c:\surnames.txt" WITH (ROWTERMINATOR='\n') DECLARE @Counter BIGINT SET @Counter = 4 WHILE (@Counter > 0) BEGIN UPDATE TableName set [last_name]= (SELECT TOP 1 FirstName from #ForeNames), [first_name]=(SELECT TOP 1 Name FROM #Name ORDER BY NEWID()), [gender]= ( SELECT TOP 1 Gender FROM #ForeNames ORDER BY NEWID()); SET @Counter=@Counter-1 END DROP TABLE #Name DROP TABLE #ForeNames SELECT * FROM TableName </code></pre> What Happens is all the rows in the table are updated with the same values and each time i execute the query they are updated with the new set of values. What I want is to loop through each row and update it and den update the next row with the other set of random name. But here it is updating the same random name across all the rows of the table. Any help would be appreciated.
Tags
<sql><sql-server>
Title
Update specific columns in a table iteratively (Do a bulk update)
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USuser2471553
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.