CODEWITHSUNDEEP
sqlsql-servert-sql
Bear
Bear

Reputation: 1107

Multi update query

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)

Upvotes: 1

Views: 142

Answers (4)

Sudhir Panda
Sudhir Panda

Reputation: 784

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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]

Upvotes: 3

Muhammad Muazzam
Muhammad Muazzam

Reputation: 2800

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

Upvotes: 1

jake
jake

Reputation: 36

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

Upvotes: 1

Related Questions