Sql-server – Update a dynamic number of rows with an amount proportionally

sql serverupdate

Since my English are not perfect I will try explaining with an example

I have tableA that I want to update the Amount

groupID reservationID  Date        Amount
26      1              2018-06-01  NULL
26      2              2018-06-01  NULL
26      3              2018-06-01  NULL
27      4              2018-06-04  NULL
27      5              2018-06-04  NULL

And tableB that holds the total amount per Group

groupID  Date        Amount
26       2018-06-01      60
27       2018-06-04     100

I trying to find a way that

groupID 26 amount of 60 can updated the first table
groupID26 rows with 20 each (60 / 3)
groupID 27 amount of 100 can update the first table groupID27 rows with
50 each (100/2)

In other words a query that will be smart enough to divide the total amount in tableB the in the number of rows in tableA where:

tableA.Date = tableB.Date
AND tableA.groupId = tableB.groupID

Thanx

Here is a sample code ddl

DECLARE @tableA TABLE
(group_id       numeric(10,0)   null,
 reservation_id numeric(10,0)   null,
 ondate         datetime        null,
 amount         numeric(10,2)   null)

DECLARE @tableB TABLE
(group_id       numeric(10,0)   null,
 ondate         datetime        null,
 amount         numeric(10,2)   null)

INSERT @tableA
SELECT 26,1,'2018-06-01',NULL
INSERT @tableA
SELECT 26,2,'2018-06-01',  NULL
INSERT @tableA
SELECT 26,3,'2018-06-01',  NULL
INSERT @tableA
SELECT 27,4,'2018-06-04',  NULL
INSERT @tableA
SELECT 27,5,'2018-06-04',  NULL

INSERT @tableB
SELECT 26,'2018-06-01',60
INSERT @tableB
SELECT 27,'2018-06-04',100

Best Answer

You can solve it this way:

declare @a table 
(groupID int, reservationID  int, [Date] date, Amount decimal(10,2));

insert into @a values 
(26,      1,              '2018-06-01',  NULL),
(26,      2,              '2018-06-01',  NULL),
(26,      3,              '2018-06-01',  NULL),
(27,      4,              '2018-06-04',  NULL),
(27,      5,              '2018-06-04',  NULL);

declare @b table 
(groupID  int, [Date] date,  Amount decimal(10,2));

insert into @b values
(26,       '2018-06-01',      60),
(27,       '2018-06-04',     100);

with cte as
(
select a.*,
       b.Amount / count(a.reservationID) over (partition by a.Date, a.groupId) new_amount
from @a a join @b b 
        on a.Date = b.Date
        AND a.groupId = b.groupID
)

update cte
set Amount = new_amount;

select *
from @a;

Related Solutions

Sql-server – How to update ID=null values in table to incremental counter values

You can do this.

WITH T
     AS (SELECT ISNULL((SELECT MAX(ID) FROM StagingTable), 0) + 
                    ROW_NUMBER() OVER (ORDER BY NaturalID) AS New_ID,
                ID
         FROM   StagingTable
         WHERE  ID IS NULL)
UPDATE T
SET    ID = New_ID

So the windowed function is used in the SELECT list but you can still use the result of it to UPDATE the column.

You should probably have a filtered index unique constraint on ID WHERE ID IS NOT NULL to prevent duplicates too. Or run this at serializable isolation level to block concurrent inserts.

SQL Server – Should INSERT Cause Exclusive Lock on Foreign Key?

Here's my first recommendation to remove the deadlock (and this is a very common cause with the same resolution every time). Your RoutePlan_Save procedure currently has this logic:

IF EXISTS (SELECT ... WHERE key = @key)
  UPDATE ... WHERE key = @key;
ELSE
  INSERT(key) VALUES(@key);

So you actually have two queries here that require locks on the table, regardless of whether you are ultimately going to perform an insert or update. Stop doing that! What is the point of checking if a row exists, and then in a separate query, firing an update statement that again has to check if the row exists? This requires more resources, not less, than just trying to update the row not knowing whether it exists. So do this instead:

BEGIN TRANSACTION;
UPDATE ... WHERE key = @key;
IF @@ROWCOUNT = 0
BEGIN
  INSERT(key) VALUES(@key);
END
COMMIT TRANSACTION;

You can also isolate the other read-only processes by using a different isolation level that doesn't require them to wait for write operations to complete. NOLOCK / READ UNCOMMITTED is a popular one, but READ COMMITTED SNAPSHOT - assuming you can take the hit on tempdb - is way better IMHO. There are other alternatives, too. I happened to blog about this just yesterday:

Bad habits : Putting NOLOCK everywhere

Best Answer

Related Solutions

Sql-server – How to update ID=null values in table to incremental counter values

SQL Server – Should INSERT Cause Exclusive Lock on Foreign Key?

Related Question