Help for SQL statement!

[Mayo123]

I have two tables (table1 and table2). Both have common fields such as LName, FName and ssn. LName and FName in table1 are blank.
Now I need to insert FName and LName from Table2 into Table1 (where table1.ssn=table2.ssn). How can I accomplish this?

Thank you in advance

 

[gmmastros]

Make sure you backup your database before running this.

If this works, and you would like me to explain it, just let me know.

Update Table1
Set    Table1.LName = Table2.LName,
       Table1.FName = Table2.FName
From   Table1
       Inner Join Table2
         On Table1.ssn = Table2.ssn

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

You have the idea, now just translate it into SQL. Try it and post back if you have any questions.

 

[Mayo123]

Thank you so much---It worked!
One thing I don't understand is your statement line4 From Table1, if skipping this line, it didn't work. How come?

 

[jbenson001]

if skipping this line, it didn't work. How come?

You need a From clause to tell it what table you are woking with.

 

[Mayo123]

Thanks again! Have a great holiday!

 

[gmmastros]

A simple update statement doesn't require a FROM clause at all.

Ex:

[tt][blue]
Update Employee
Set Salary = Salary + 200
Where Name = 'George'
[/blue][/tt]

Another way to write this would be...


[tt][blue]
Update Employee
Set Salary = Salary + 200
[!]From Employee[/!]
Where Name = 'George'
[/blue][/tt]

Since there is only 1 table involved, you do not need to include a from clause. When updating data in one table [!]based[/!] on data from another table, you must include the from clause and specify how the tables are joined.

Does this make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Mayo123]

Wonderful explanation, George! Thanks!