Updating one table from another 1

[Guggly]

Hi! I'm new to SQL Server, though I've been using Microsoft Access for years.

I'm trying to update a table based on the value of another table. In Access I can create a join between the two tables and update the value but without a problem, but this doesn't seem to work with SQL Server.

When I try to do this in a text Stored Procedure I get a join error and when I use the query builder from Access' frontend for SQL Server it tells me "An Update query affects only a single table. Select the table to update", thereby removing the other table that I'm not updating.

So, the bottom line; how can I update a table based on the content of another table? Thanks! -- Mike

 

[SQLSister]

Code Example:

UPDATE table1
SET field1 = table2.field1,
field2 = table2.field2,
field3 = table2.field3
FROM table1
INNER JOIN table2
ON table1.id = table2.id

Questions about posting. See faq183-874

 

[Guggly]

That's great thanks! Now, how can I run multiple updates in one stored procedure? You can do that right?

With the first update I'm updating a field based on set criteria, then I need to update the field again with different criteria, thus affecting different rows. Can I do that without having to have several procedures (and without feeding function variables into the one)?

Thanks! -- Mike

 

[SQLSister]

Sure just write differnt updates and put them in the same SP. If they are to related tables or in some other way related you will want to look up how to transacation processing, so you can roll back all if there is a problem.

You may be able to handle multiple criteria through using the CASE statment. Something like;

UPDATE table1
SET field1 = case when table2.field1 < 10 then table2.field2
when table2.field2 = 10 then Table2.field1
else table2.field + 10 end ,
field2 = table2.field2,
field3 = table2.field3
FROM table1
INNER JOIN table2
ON table1.id = table2.id

Questions about posting. See faq183-874

 

[Guggly]

Thank you, that's very helpful, and I'll see if using CASE will get me the results I need for the update query.

What's generally faster, evaluating several Case scenarios or running a few (5) Update procedures? Not that it would make much difference here, since I'm only working with a few thousand records, but from the learning perspective it would be interesting to know. Thanks for your help! -- Mike