Update table based on a select from another

Question

I have this select statement below which I would like to use to update the quantity of the products in another table, tablex. I cannot seem to figure out how to match the product number from this query to the productnumber tablex and then add the quantity found in this statement to the existing quantity in tablex.

select 
    p.ProductNumber, sod.Quantity ,so.StateCode
from 
    SalesOrderDetail sod
right join 
    ProductAssociation pa on sod.ProductId = pa.ProductId
left join 
    Product p on pa.AssociatedProduct = p.ProductId
left join 
    SalesOrder so on so.SalesOrderId = sod.SalesOrderId
where 
    so.StateCode = '3'

score 0 · Answer 1 · answered Jul 17 '13 at 20:15

0

You can have update base on multiple tables, the syntax is something like

update tablex
set tablex.quantity = sod.Quantity
from tablex join product p on tablex.productnumber = p.ProductNumber
join... -- add the rest of your joins and conditions.

answered Jul 17 '13 at 20:15

Yuriy Galanter

38,833
15
69
136

score 0 · Answer 2 · edited May 23 '17 at 11:49

Are you looking for this?

UPDATE tx SET tx.Quantity = tx.Quantity + sod.Quantity FROM 
from SalesOrderDetail sod
right join ProductAssociation pa on sod.ProductId=pa.ProductId
left join Product p on pa.AssociatedProduct=p.ProductId
left join SalesOrder so on so.SalesOrderId=sod.SalesOrderId
left join tablex tx on p.ProductNumer = tx.ProductNumber
where so.StateCode='3'

How can I do an UPDATE statement with JOIN in SQL?

Hart CO · Answer 3 · 2013-07-17T20:25:29.457

Basic syntax for UPDATE with a JOIN:

UPDATE A
SET A.foo = B.bar
FROM TableA A
JOIN TableB B 
    ON A.col1 = B.colx

So I believe you're after something like:

UPDATE A
SET A.Quantity = B.Quantity + A.Quantity
FROM Tablex A
JOIN (select p.ProductNumber, sod.Quantity ,so.StateCode
      from SalesOrderDetail sod
      right join ProductAssociation pa on sod.ProductId=pa.ProductId
      left join Product p on pa.AssociatedProduct=p.ProductId
      left join SalesOrder so on so.SalesOrderId=sod.SalesOrderId
      where so.StateCode='3'
      )B
   ON A.ProductNumber = B.ProductNumber

Not sure how your StateCode factors in, additional JOIN criteria perhaps?

This would replace and not update/add quantity..I think it should be `A.Quantity = A.Quantity + B.Quantity` — Jafar Kofahi, Jul 17 '13 at 20:23

score 0 · Answer 4 · answered Jul 17 '13 at 20:17

I'm guessing that you're trying to update a large quantity of rows in TableX so I'm going to suggest a way to do them all at once rather than one at a time.

update TableX
 set quantity = quantity +
 (select sod.quantity from SalesOrderDetail where sod.ProductId = TableX.ProductId)

You might wish to do this on a subset of SalesOrderDetail, and that's fine, just use the WHERE clause for that.

score 0 · Answer 5 · answered Jul 17 '13 at 20:19

Try

UPDATE tablex
SET Quantity= Quantity +
(SELECT sod.Quantity  FROM SalesOrderDetail sod
RIGHT JOIN ProductAssociation pa ON sod.ProductId=pa.ProductId
LEFT JOIN Product p ON pa.AssociatedProduct=p.ProductId
LEFT JOIN SalesOrder so ON so.SalesOrderId=sod.SalesOrderId
WHERE so.StateCode='3' AND p.ProductNumber=Tablex.productnumber)

Update table based on a select from another

5 Answers5