Multi update query

Question

I've created two temp tables. One with Orders which contains Article and Quantity and the other one with availability where we also have Article and Quantity. I would like to write a multi update query with subtracking order quantity from stock and from itself for all articles in temporary table with Orders. As far as I know it is not possible to alter two fields from different tables in one update query.

I've tried something like this, but it's of course doesn't work.

UPDATE  #Stocks as s
  INNER JOIN #Orders as o on o.ArticleId=s.ArticleId
SET 
   s.Quantity = (s.Quantity - o.Quanity)

FROM
    #Stocks s
   JOIN #Orders o on o.ArticleId=s.ArticleId
WHERE
   #Stocks.ArticleId IN (SELECT ArticleId FROM #Orders)

Possible duplicate of [How to update two tables in one statement in SQL Server 2005?](http://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005) - The short of it is, you would probably need to do two updates using a transaction. — gmiley, Dec 14 '15 at 14:27

Gordon Linoff · Answer 1 · 2015-12-14T14:47:06.577

When do you an update using a join with multiple matches, only one arbitrary row is chosen for the update. The key idea is to aggregate the data before the update:

UPDATE s
    SET Quantity = (s.Quantity - o.Quanity)
    FROM #Stocks s JOIN
         (SELECT o.ArticleId, SUM(o.Quantity) as quantity
          FROM #Orders o
          GROUP BY o.ArticleId
         ) o
         ON o.ArticleId = s.ArticleId;

Your statement is way over-complicated, mixing update syntax from SQL Server, MySQL, and Postgres. In addition, the WHERE clause is unnecessary because the JOIN does the filtering. However, even once the syntax errors are fixed, you will still have the problem of calculating incorrect results, unless you pre-aggregate the data.

Unfortunately, the description of this behavior is buried deep in the documentation of the first example on the update page:

The previous example assumes that only one sale is recorded for a specified salesperson on a specific date and that updates are current. If more than one sale for a specified salesperson can be recorded on the same day, the example shown does not work correctly. The example runs without error, but each SalesYTD value is updated with only one sale, regardless of how many sales actually occurred on that day. This is because a single UPDATE statement never updates the same row two times. [emphasis added]

jake · Answer 2 · 2015-12-14T14:35:47.523

1

How about this?

UPDATE s  
   SET s.Quantity = (s.Quantity - o.Quanity)  
  FROM #Stocks as s  
  INNER JOIN #Orders as o on o.ArticleId=s.ArticleId

edited Dec 14 '15 at 14:35

answered Dec 14 '15 at 14:28

jake

36
4

score 1 · Answer 3 · answered Dec 14 '15 at 14:39

1

For updating two tables using single query, you should create a view that contain both tables columns and then update that view.

answered Dec 14 '15 at 14:39

Muhammad Muazzam

2,810
6
33
62

1

Why would it be necessary to first create a view? – Lamak Dec 14 '15 at 14:41
@Lamak As updating two tables in single query is not possible without this. – Muhammad Muazzam Dec 14 '15 at 14:42
This was my understanding of the question, the asker seems to want to update two fields in different tables in one query. While this could work, instead of creating a database object, simply wrap two updates in a transaction, unless something is preventing that. – gmiley Dec 14 '15 at 14:52
@MuhammadMuazzam . . . There is no evidence in the question that the OP wants to update two tables. – Gordon Linoff Dec 14 '15 at 15:16
@GordonLinoff The sentence: " As far as I know it is not possible to alter two fields from different tables in one update query. " seems to suggest it... The problem is, the attempted statement the user posted does not indicate that problem, however that statement, by the posters admission is known to be incorrect. – gmiley Dec 14 '15 at 15:35

score 0 · Answer 4 · answered Dec 14 '15 at 14:52

0

Your Question is all about Multi Update, but updation perform in one table based on another table so to do this use join

But if updation perform in two or more table we have to create view then we can update

thanks

answered Dec 14 '15 at 14:52

Sudhir Panda

774
1
7
27

Multi update query

4 Answers4