SQL Update to the Sum of Its Joined Values

SQL Update to the SUM of its joined values

How about this:

UPDATE p
SET p.extrasPrice = t.sumPrice
FROM BookingPitches AS p
INNER JOIN
    (
        SELECT PitchID, SUM(Price) sumPrice
        FROM BookingPitchExtras
        WHERE [required] = 1
        GROUP BY PitchID 
    ) t
    ON t.PitchID = p.ID
WHERE p.bookingID = 1

Update one table based upon SUM(values) in another table on multiple criteria

Try this solution:

UPDATE m
SET m.Foo = f.valsum
FROM [MASTER] m
INNER JOIN
(
  SELECT ID, CCY, SUM(val) valsum
  FROM Foos
  GROUP BY  ID, CCY 
) f ON m.ID = f.ID AND m.CCY  = f.CCY;

SQL Update with SUM from other fields

Why not just do APPLY with update :

UPDATE m
     SET m.total_number = s.sum_no
FROM master m CROSS APPLY
     (SELECT SUM(t.number) as sum_no
      FROM things t
      WHER t.M_ID = m.M_ID
     ) s;
WHERE m.M_ID = 1234;

However, your update query needs to be correlated so, it would be :

UPDATE m
     SET M.total_number = (SELECT SUM(t.number) 
                           FROM things t
                           WHERE t.M_ID = m.M_ID
                          )
FROM master m 
WHERE m.M_ID = 1234;

Updating table with select sum from another table

You can join the table orders to the query that returns all the sums from order_items:

UPDATE orders o
INNER JOIN (
    SELECT order_id, FORMAT(SUM(total), 2) AS xpto_spent
    FROM order_items
    WHERE brand = 'XPTO'
    GROUP BY order_id
) t ON o.order_id = t.order_id
SET o.xpto_spent = t.xpto_spent

SQL How to Update SUM of column over group in same table

Assuming you are using SQL Server, I think you want something like this:

WITH toupdate AS
     (SELECT team, year, 
             Sum(personsales) OVER (partition BY team, year) AS newTeamSales 
      FROM salessummary
     ) 
UPDATE toupdate 
   SET teamsales = newteamsales; 

Your original query has several issues and suspicious constructs. First, an aggregation subquery is not updatable. Second, you are doing an aggregation and using a window function with, although allowed, is unusual. Third, you are aggregating by PersonSales and taking the sum(). Once again, allowed, but unusual.

How to update value of a column based on sum of same column values of other records in MYSQL?

Mysql multi table update manual page - https://dev.mysql.com/doc/refman/8.0/en/update.html

eg

drop table if exists t;

CREATE TABLE T(ID INT,SUBJECT VARCHAR(10), MARK INT);

INSERT INTO T VALUES
(1,'ALL',NULL),(1,'AAA',1),(1,'BBB',2),
(2,'ALL',NULL),(2,'AAA',10),(2,'BBB',20);

UPDATE T 
JOIN
 (SELECT ID,SUM(MARK) MARK FROM T WHERE SUBJECT <> 'ALL' GROUP BY ID) S ON S.ID = T.ID
SET T.MARK = S.MARK
WHERE SUBJECT = 'ALL';

Update a record using the sum of it's value and another

If you know record keys:

DECLARE @Id1 INT = 10, @Id2 INT = 11

;WITH CTE AS
(
  SELECT @Id2 Id, SUM(allocated) Allocated, SUM(purchased) Purchased, 
         SUM(installed) Installed
  FROM YourTable
  WHERE pk_id IN (@Id1 ,@Id2)
)
UPDATE t SET allocated = c.Allocated, purchased = c.Purchased, 
             installed = c.Installed
FROM YourTable t
         JOIN CTE c ON t.pk_id = c.id

Using SUM function in an update query with inner joins

You should use the following syntax when using an aggregate in an update statement.

UPDATE t1
 SET t1.field = t2.field2Sum
 FROM table1 t1
 INNER JOIN (select field3, sum(field2) as field2Sum
    from table2
       group by field3) as t2
 on t2.field3 = t1.field3  

See Below, I only scripted a few tables but you can see how to do the rest.

UPDATE FlightBooking set TotalCost = (ExtraCost.SumExtra + SumLuggage)  From FlightBooking 
INNER JOIN Passenger ON Passenger.FlightBookingId=FlightBooking.FlightBookingId 
INNER JOIN AirplaneSeat ON AirplaneSeat.AirplaneSeatId =  Passenger.SeatId
INNER JOIN Section ON AirplaneSeat.SectionId = Section.SectionId
INNER JOIN (Select FlightBookingId, sum(ExtraCost) as SumExtra from Extracost Group by FlightBookingId) as  ExtraCost
     ON ExtraCost.FlightBookingId=FlightBooking.FlightBookingId
INNER JOIN (Select FlightBookingId, sum(Price) as SumLuggage from Luggage Group by FlightBookingId) as  Luggage
     ON Luggage.FlightBookingId=FlightBooking.FlightBookingId

Update Table with sum based on column value

Try this modified query:

UPDATE Result o
INNER JOIN (
    SELECT col1, SUM(col2) AS sumcol
    FROM Result
    GROUP BY col1
) i ON o.col1 = i.col1
SET o.result = i.sumcol

---

Related Topics