Updateing records based on Select?

[Solo4357]

I got one statement that runs off of a table and I want to put the results into another table. Table Job and table Sales.

I got a statement that runs on table job and aggregates some totals for me. It spits out a Job number and a billed summary. The billed isn't a field but a sum of the billing field.

I want to take that statement and update table Sales. The fields are the Invoice total with the billed value whereever Jobnumber matches with Account number. Any good and easy ways to do that? Thanks

 

[SQLSister]

here is some sample code on how to make a select into an update:

update a
set field1 = b.field2
--select a.field1, b.field2, a.idfield
from table1 a 
join table2 b on a.idfield = b.idfield
where a.field1 is null

As you can see from the above, all you really need to do is delete the select and the field list from the query and add the update and set field = part of the query.

I usually write them like this so I can run the select before running the update to make sure I'm getting what I want but use the comment block so that when I am ready to run the update for real it will run. After testing I can eliminate the commented line.


"NOTHING is more important in a database than integrity." ESquared

 

[Solo4357]

So is this all in one statement? I'm sorry, I'm confused by what you mean.. delete the select?

 

[SQLSister]

see the select line is commented out? It is there for testing purposes only and to show you how to convert a selct to an update. It is not necessary for the actual running of the code and can be deleted when you are finished testing. But only delete the line of code that is commented out.

"NOTHING is more important in a database than integrity." ESquared

 

[Solo4357]

So to clarify, is this my code?

update a
set field1 = b.field2
from table1 a 
join table2 b on a.idfield = b.idfield
where a.field1 is null

 

[SQLSister]

yes, assumming of course that you substitute your table and field names and add whatever where conditions you need.

"NOTHING is more important in a database than integrity." ESquared

 

[Solo4357]

ok, I'll give it a try. Thanks!