SQL Server – How to Update with Inner Join

joinsqlsql-server-2005t-sql

I have 3 tables (simplified):

 tblOrder(OrderId INT)  
  tblVariety(VarietyId INT,Stock INT)  
  tblOrderItem(OrderId,VarietyId,Quantity INT)

If I place an order, I drop the stock level using this:

UPDATE tblVariety  
SET tblVariety.Stock = tblVariety.Stock - tblOrderItem.Quantity  
FROM tblVariety  
INNER JOIN tblOrderItem ON tblVariety.VarietyId = tblOrderItem.VarietyId  
INNER JOIN tblOrder ON tblOrderItem.OrderId = tblOrder.OrderId  
WHERE tblOrder.OrderId = 1

All fine, until there are two rows in tblOrderItem with the same VarietyId for the same OrderId. In this case, only one of the rows is used for the stock update. It seems to be doing a GROUP BY VarietyId in there somehow.

Can anyone shed some light? Many thanks.

Best Answer

My guess is that because you have shown us simplified schema, some info is missing that would determine why have the repeated VarietyID values for a given OrderID.

When you have multiple rows, SQL Server will arbritrarily pick one of them for the update.

If this is the case, you need to group first

UPDATE V
SET
   Stock = Stock - foo.SumQuantity
FROM
    tblVariety V
    JOIN
    (SELECT SUM(Quantity) AS SumQuantity, VarietyID
     FROM tblOrderItem
      JOIN tblOrder ON tblOrderItem.OrderId = tblOrder.OrderId  
     WHERE tblOrder.OrderId = 1
     GROUP BY VarietyID
    ) foo ON V.VarietyId = foo.VarietyId

If not, then the OrderItems table PK is wrong because if allows duplicate OrderID/VarietyID combinations (The PK should be OrderID/VarietyID, or these should be constrained unique)

Generic example

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

SQL Server Update with Join – How to Perform

Syntax strictly depends on which SQL DBMS you're using. Here are some ways to do it in ANSI/ISO (aka should work on any SQL DBMS), MySQL, SQL Server, and Oracle. Be advised that my suggested ANSI/ISO method will typically be much slower than the other two methods, but if you're using a SQL DBMS other than MySQL, SQL Server, or Oracle, then it may be the only way to go (e.g. if your SQL DBMS doesn't support MERGE):

ANSI/ISO:

update ud 
     set assid = (
          select sale.assid 
          from sale 
          where sale.udid = ud.id
     )
 where exists (
      select * 
      from sale 
      where sale.udid = ud.id
 );

MySQL:

update ud u
inner join sale s on
    u.id = s.udid
set u.assid = s.assid

SQL Server:

update u
set u.assid = s.assid
from ud u
    inner join sale s on
        u.id = s.udid

PostgreSQL:

update ud
  set assid = s.assid
from sale s 
where ud.id = s.udid;

Note that the target table must not be repeated in the FROM clause for Postgres. Main question: How to do an update + join in PostgreSQL?

Oracle:

update
    (select
        u.assid as new_assid,
        s.assid as old_assid
    from ud u
        inner join sale s on
            u.id = s.udid) up
set up.new_assid = up.old_assid

SQLite:

update ud 
     set assid = (
          select sale.assid 
          from sale 
          where sale.udid = ud.id
     )
 where RowID in (
      select RowID 
      from ud 
      where sale.udid = ud.id
 );

SQLite 3.33 added support for an UPDATE + FROM syntax analogous to the PostgreSQL one:

update ud
  set assid = s.assid
from sale s 
where ud.id = s.udid;

Main question: Update with Join in SQLite

Best Answer

Related Solutions

SQL – SQL Update Query Using Joins

Generic example

SQL Server Update with Join – How to Perform

Related Question