Noob Stored Proc question - select and update tables 2

[jacktripper]

Well, I'm not a complete noob with stored procedures, but it is certainly not my strong point...

I have two tables of employee data that want to combine parts of them together. How can I write a stored procedure that steps through and selects data from one table, and then updates the appropriate data in the other?

Basically what the stored proc to do is:

SELECT employeeID, field1, field2, field3 FROM table1

CombinedFields = field1 + field2 + field3

UPDATE table2 SET NewField = CombinedFields WHERE table2.employeeID = employeeID

And do the above for each row in the table...

Thanks in advance for any ideas...!

 

[RiverGuy]

UPDATE table2
SET NewField = table1.Field1 + table1.Field2 + table1.Field3
FROM table2
INNER JOIN table1
ON table2.EmployeeID = table1.EmployeeID

 

[gmmastros]

You're probably thinking that you need to get all loopy to do this, but you don't. If I understand correctly...

Update Table2
Set    Table2.CombinedFields = Table1.Field1 + Table1.Field2 + Table1.Field3
From   Table2
       Inner Join Table1
         On Table2.employeeID = Table1.EmployeeID

Before running this, I encourage you to convert it to a select to make sure it is doing the right thing, like this:

--Update Table2
--Set    Table2.CombinedFields = Table1.Field1 + Table1.Field2 + Table1.Field3
Select Table2.employeeID, Table1.Field1 + Table1.Field2 + Table1.Field3
From   Table2
       Inner Join Table1
         On Table2.employeeID = Table1.EmployeeID

Also, it's probably a good idea to make sure you have a backup before updating the data.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jacktripper]

Yes, i was thinking i'd have to do a cursor and maybe a case statement, etc. I tend to think more in loops, I guess.

I'll make a backup and give this a try today, we shall see.