row by row updation of a table in sql server...

Question

Suppose I have table name table1 like this with 3.9 million records

ID                       Firstname                 Lastname                   UserName

1                         Rajib                                 pal                                  NULL

2                        Bubun                        Ghosh                               NULL

………      and so on

Now if ‘Rajib pal’ occurs 25 times in that table… then we have a particular logic to update username of 25 rows. In case of ‘Rajib pal’ (25 occurrences) total 14 logic will be satisfied…and then loop directly goes to the 11 to 14 condition to maintain the uniqueness of username and loop will running until the 25 rows are not updated..

this query is only for explaining the logic for update username...it does not work..

please help..its really needed..

Declare @FName varchar(250)

Declare @LName Varchar(250)

Declare @No_of_occurance int

select @FName = FirstName, @LName = LastName, @No_of_occurance = COUNT(*)  from Table1

group by FirstName,LastName

order by COUNT(*)

Begin

declare @i int = 1

                   

                  While @i < @No_of_occurance

                  --- 1. User Name is the first name of the user

                              Begin

                                    Update Table1 set Gofba_UserName =  Lower(@FName) where FirstName = @FName and LastName = @LName 

                                    set @i = @i + 1

                            end

                 

                  --- 2. Concatenation  of First name and last name

                  if @i<=@No_of_occurance

                  begin

                                   

                                    Update Table1 set Gofba_UserName = Lower(@FName) + + Lower(@LName) where FirstName = @FName and LastName = @LName

                                    set @i = @i + 1

                  end

                  --- 3.      Using ‘_’ between First name and last name

                  if @i<=@No_of_occurance

                  begin

     

                                    Update Table1 set Gofba_UserName = Lower(@FName) + '_' + Lower(@LName) where FirstName = @FName and LastName = @LName

                                    set @i = @i + 1

                             

                  end

                  --- 4.  Using ‘.’ between First name and last name

                  if @i<=@No_of_occurance

                  begin

                                    Update Table1 set Gofba_UserName = Lower(@FName) + '.' + Lower(@LName) where FirstName = @FName and LastName = @LName

                                    set @i = @i + 1

                             

                  end

                  --- 5.      Extract 1st(first) character from First name and then add it with last name

                  if @i<=@No_of_occurance

                  begin

                                    Update Table1 set Gofba_UserName = Lower(Left(@FName,1)) + + Lower(@LName) where FirstName = @FName and LastName = @LName

                                    set @i = @i + 1

                  end

                  --- 6.      Extract 1st(first) character from First name and then Using ‘.’ between Extracted expression and last name

                  if @i<=@No_of_occurance

                  begin

                                    Update Table1 set Gofba_UserName = Lower(Left(@FName,1)) + '_' + Lower(@LName) where FirstName = @FName and LastName = @LName

                                    set @i = @i + 1

                             

                  end

                  --- 7. Extract 1st(first) character from First name and then Using ‘_’ between Extracted expression and last name

                  if @i<=@No_of_occurance

                  begin

                                    Update Table1 set Gofba_UserName = Lower(Left(@FName,1)) + '.' + Lower(@LName) where FirstName = @FName and LastName = @LName

                                    set @i = @i + 1

                             

                  end

                  --- 8. Concatenation  of last name and First name

                  if @i<=@No_of_occurance

                  begin

                       

                                    Update Table1 set Gofba_UserName = Lower(@LName) + + Lower(@FName) where FirstName = @FName and LastName = @LName

                                    set @i = @i + 1

                             

                  end

                  --- 9. Using ‘_’ between last name and First name

                  if @i<=@No_of_occurance

                  begin

                       

                                    Update Table1 set Gofba_UserName = Lower(@LName) + '_' + Lower(@FName) where FirstName = @FName and LastName = @LName

                                    set @i = @i + 1

                  end

                  --- 10. Using ‘.’ between last name and First name

                  if @i<=@No_of_occurance

                  begin

                                    Update Table1 set Gofba_UserName = Lower(@LName) + '.' + Lower(@FName) where FirstName = @FName and LastName = @LName

                                    set @i = @i + 1

                  end

                  --- 11. Adding random years with first name

                  if @i<=@No_of_occurance

                  begin

                                    Update Table1 set Gofba_UserName = Lower(@FName) + (select top 1 CAST(Year as varchar) from [All_Years] order by NEWID())

                                    where FirstName = @FName and LastName = @LName

                                    set @i = @i + 1

                  end

                  ----12. Concatenation  of First name and last name and then add years

                  if @i<=@No_of_occurance

                  begin

                                    Update Table1 set Gofba_UserName = Lower(@FName)+ Lower(@LName)+(select top 1 CAST(Year as varchar) from [All_Years] order by NEWID())

                                    where FirstName = @FName and LastName = @LName

                                    set @i = @i + 1

                             

                  end

                  --- 13. Using ‘.’ between First name and last name and then add years

                  if @i<=@No_of_occurance

                  begin

                                    Update Table1 set Gofba_UserName = Lower(@FName)+ '.' + Lower(@LName)+(select top 1 CAST(Year as varchar) from [All_Years] order by NEWID())

                                    where FirstName = @FName and LastName = @LName

                                    set @i = @i + 1

                  end

                  --- 14. Using ‘_’ between First name and last name and then add years (If it is not exists)

                  if @i<=@No_of_occurance

                  begin

                                    Update Table1 set Gofba_UserName = Lower(@FName)+ '_' + Lower(@LName)+(select top 1 CAST(Year as varchar) from [All_Years] order by NEWID())

                                    where FirstName = @FName and LastName = @LName

                                    set @i = @i + 1

                             

                  end

             

            End                                                                                                                                                                                                       

Answer 1

Not sure of your resultset. Could you please provide us the table structure and sample data along with the result.

I dont think you need a multiple if here... Please provide more details.

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

Hi,

if I understand you correctly you can use CTE or just COUNT group by user name and make join between your resultset and the basic table and then update based on this join. if you have a log in each row you can use CTE and then make the join.

I hope this is helpful.

---

Please Mark it as Answered if it answered your question
OR mark it as Helpful if it help you to solve your problem
Elmozamil Elamir Hamid

MCTS: SQL Server Administration/Development

MyBlog

Answer 3

Hi Latheesh NK..

How could I send the table and resultset??

Answer 4

Can  you show the desired result?

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 5

Your code doesn't make much sense, can you post your table, updating rules and desired result?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

create script and insert script and send us a view of your desired result.

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 7

>> Suppose I have table name table1 [why is this a great name !??] 
with 3.9 million records [sic: rows are not records] <<

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

This is minimal polite behavior on SQL forums. Why do you think anything you have done it right? I saw nothing done right. 

You have been told in a public forum that you are totally incompetent to do RDBMS by one of the authors of the language you are using. Nothing you did was right!!! NOTHING. You did not even spell “occurance” (Sp: occurrence) right !!

The total crap you posted is procedural code. You have nothing right. Nothing!

You need to start over or get out of programming; yes, this is that awful! You are like a bad doctor; you will hurt or kill the clientele! 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 8

That was a severe rebuke Joe but it could have been worse..

I don't see any dynamic SQL at all! Plus there are at least 10 lines of comments. Those comments amount to more documentation than you sometimes get in entire *solutions* - we've got >10 in just one code block!

What does that code do anyway? *cough*

---

Thanks! Josh Ash