Update only one column in SQL table

[Shweta]

Hi All,

I have a table with approx 200 records for Advisor details. I have added a new column 'Email_Id'. EmailId is First_Name.Last_name@domain.com. Please help with a query to loop through all records to update this column.

First_Name and Last_Name is already their in the table.

Thanks in advance!

Regards,
Shweta Jain

 

[mole999]

if you left it with the names, then you can add the domain in your sql script i.e first + ',' + last + '@domain.com' as you query

 

[Shweta]

Thanks mole999. But I don't know how to do it for 200 records at once. Please help with a complete script.

 

[james_lankford]

he's saying that instead of adding the new column you could just change your queries

every time you need the email from the table just write
select tbl.First_Name & "." & Last_Name & "@domain.com" from tbl

that way you build email every time you need it
this has a big advantage
let's say one of your employees gets married and changes their last name

your way you'd have to change the Last_Name column and you'd also have to change the new Email_Id column

his way you'd only have to change the Last_Name column

because the email address would be automatically built using the corrected Last_Name every time you query the able

 

[Shweta]

Thanks for this. I know this alternative. However my query was something different.

I just want to clear the concept of looping through all the records in an existing SQL table. In case I have to add a new column in an existing table then what's the solution. Do I need to write update statement 200 times or there is any other option using loop or something else. So that I can start a loop from 1 to 200 and write update statement within this loop. Please suggest.

 

[james_lankford]

a simple update statement should do it




first try

select 
 tbl.First_Name & '.' & Last_Name & '@domain.com' as [email_id]
from 
  tbl

if the results look correct then run

update  
  tbl 
set 
 Email_Id = tbl.First_Name & '.' & Last_Name & '@domain.com'

running the update one time will fill the column for every row


Microsoft Access Update Query Examples, SQL Syntax, and Errors


SQL: UPDATE Statement

 

[Shweta]

Thank you...fine with me

 

[Shweta]

One more query..In this case First Name and Last Name was available in the table. So it was quite easy to update the data in the newly added column by giving the reference of existing columns and adding domain name at the end.

What if I need to update bank account numbers or some other detail which is unique for each advisor in the table?