CODEWITHSUNDEEP
sqlsql-serversql-update
Henning
Henning

Reputation: 31

SQL update for multiple database entries

we are using a commercial application, which is customizable. Front End is a webserver with MS SQL server in background.

We have an asset management, which with we can link some contracts.

Now I have to create a new workflow: An asset has a costcenter and all linked contracts to this object have to migrate the costcenter dialy night automatically.

For example that's my view "View_Info" to get needed information:

IDAsset - IDContract - ConstCenterAsset

111       222          333
111       223          333
112       224          334
113       225          335
....

And my main table "Contract":

ID - CostCenter

222    000
223    000
224    000
225    000

I know how to update one entry in "Contract" table with SQL UPDATE command...

But how can I do it for all existing entries...

I have to update about 1000 DB entries dialy night...

Upvotes: 3

Views: 117

Answers (1)

user9121459
user9121459

Reputation:

You can UPDATE with JOIN like this:

UPDATE c 
SET c.CostCenter = v.ConstCenterAsset
FROM Contract as c 
INNER JOIN View_Info as v ON v.IDContract = c.ID;

This way, all the table Contract' entries will be updated from the view View_Info. You can also add extra WHERE clause at the end to limit the entries which should be updated.

Upvotes: 1

Related Questions