CODEWITHSUNDEEP
sql-serversql-server-2008
HashCoder
HashCoder

Reputation: 946

Update row with condition in SQL Server 2008

This is my table structure:

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)

I have to reduce the available credit column value, I have amount 1050 with me. I need to reduce 1050 from credit table where organisation id = 1. Here the organisation Id 1 have 1100 available credit in total. The condition is the first inserted credit row should be updated first and then the rest (FIFO model), also the updation should happen only for organisationId = 1.

How do we update this using a single or multiple update statement?

Any suggestions?

Upvotes: 2

Views: 2841

Answers (4)

AnandPhadke
AnandPhadke

Reputation: 13486

Previously I had given only select query,Here is the final UPDATE query which does the task.

DECLARE @balance int=1050

;WITH CTE as (
select id,organisationid,CASE when @balance>availableCredit then 0 else availableCredit-@balance 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

Upvotes: 1

AnandPhadke
AnandPhadke

Reputation: 13486

Try This query:

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 availableCredit-@balance 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

Upvotes: 0

marc_s
marc_s

Reputation: 754240

Unfortunately, this is a rather messy thing to do in T-SQL - you'll need something like a loop (cursor or WHILE statement).

With this code here, I can get it to run - it's not pretty, but it works.

-- 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

Upvotes: 2

Curtis
Curtis

Reputation: 103338

This script reduces "Id 1" availableCredit by 1000, and reduces "Id 2" availableCredit by 50.

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

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

Upvotes: 1

Related Questions