更新一行在SQL Server 2008

Question

条件这是我的表结构：

CREATE table Credit(id integer, organisationid int, availableCredit int) 

INSERT INTO Credit VALUES (1, 1, 1000) 
INSERT INTO Credit VALUES (2, 1, 100) 
INSERT INTO Credit VALUES (3, 2, 600) 
INSERT INTO Credit VALUES (4, 2, 400) 

我不得不减少可用信用列值，我有量1050和我在一起。我需要从组织ID = 1的信用表中减少1050.在这里组织ID 1总共有1100个可用信用。条件是首先应该先更新插入的信用额度，然后再更新（FIFO模型），同时更新应该只发生在organisationId = 1。

我们如何使用单个或多个更新语句更新？

有什么建议吗？

Answer 1

不幸的是，这是一个相当混乱的事情在T-SQL做减少 “ID 2” availableCredit - 你会需要类似循环（光标或WHILE语句）。

在这里使用此代码，我可以让它运行 - 它不漂亮，但它的工作原理。

-- you want to reduce the total credits by this amount 
DECLARE @AmountToReduce INT = 1050 

-- temporary variables  
DECLARE @ID INT, @AvailableCredit INT 

-- get the first row from dbo.Credit that still has availableCredit - ordered by id 
SELECT TOP 1 @ID = id, @AvailableCredit = availableCredit 
FROM dbo.Credit 
WHERE availableCredit > 0 AND organisationId = 1 
ORDER BY id 

-- as long as we still have credit to reduce - loop.. 
WHILE @AmountToReduce > 0 AND @ID IS NOT NULL 
BEGIN 
    -- if we need to remove the complete availableCredit - do this UPDATE 
    IF @AmountToReduce > @AvailableCredit 
    BEGIN 
     UPDATE dbo.Credit 
     SET availableCredit = 0 
     WHERE id = @ID 

     SET @AmountToReduce = @AmountToReduce - @AvailableCredit 
    END 
    ELSE BEGIN 
      -- if the amount to reduce left is less than the availableCredit - do this UPDATE 
     UPDATE dbo.Credit 
     SET availableCredit = availableCredit - @AmountToReduce 
     WHERE id = @ID 

     SET @AmountToReduce = 0 
    END 

    -- set @ID to NULL to be able to detect that there's no more rows left 
    SET @ID = NULL 

    -- select the next "first" row with availableCredit > 0 to process  
    SELECT TOP 1 @ID = id, @AvailableCredit = availableCredit 
    FROM dbo.Credit 
    WHERE availableCredit > 0 AND organisationId = 1 
    ORDER BY id 
END 

Answer 2

该脚本由1000减少 “ID 1” availableCredit，并通过50

UPDATE Credit 
SET availableCredit=(availableCredit-1000) 
WHERE id=1 

UPDATE Credit 
SET availableCredit=(availableCredit-50) 
WHERE id=2 

Answer 3

尝试此查询：

CREATE table Credit(id integer, organisationid int, availableCredit int) 

INSERT INTO Credit VALUES (1, 1, 1000) 
INSERT INTO Credit VALUES (2, 1, 100) 
INSERT INTO Credit VALUES (3, 2, 600) 
INSERT INTO Credit VALUES (4, 2, 400) 


DECLARE @balance int=1050 

;WITH CTE as (
select id,organisationid,CASE when @balance>availableCredit then 0 else [email protected] end as availableCredit, 
CASE when @balance>availableCredit then @balance-availableCredit else 0 end as balamt from Credit where id=1 and organisationid=1 

union all 

select t.id,t.organisationid,CASE when c.balamt>t.availableCredit then 0 else t.availableCredit-c.balamt end as availableCredit, 
CASE when c.balamt>t.availableCredit then c.balamt-t.availableCredit else 0 end as balamt1 
from Credit t inner join CTE c on t.id-1=c.id and t.organisationid=1 

) 
SELECT id,organisationid,availableCredit FROM CTE 

Answer 4

以前我只给出的选择查询，这是最后的更新查询该做的任务。

DECLARE @balance int=1050 

;WITH CTE as (
select id,organisationid,CASE when @balance>availableCredit then 0 else [email protected] end as availableCredit, 
CASE when @balance>availableCredit then @balance-availableCredit else 0 end as balamt from Credit where id=1 and organisationid=1 

union all 

select t.id,t.organisationid,CASE when c.balamt>t.availableCredit then 0 else t.availableCredit-c.balamt end as availableCredit, 
CASE when c.balamt>t.availableCredit then c.balamt-t.availableCredit else 0 end as balamt1 
from Credit t inner join CTE c on t.id-1=c.id --and t.organisationid=1 

) 

Update c SET c.availableCredit = ct.availableCredit 
FROM Credit c inner join CTE ct 
on c.id=ct.id 

SELECT * FROM Credit