Update record with case statement for Sybase ASA11

Question

I'm using sybase ASA11. There's 2 table, Deposit table (Column = Balance, AccountXID) and Trx Table (Column = TimeRqTimestamp, trxresp).

I have an update syntax like this

--Update for table Deposit  
    @Amount = 2000
    @Accountxid = 123    
    
    update Deposit set Balance = Balance - @Amount where AccountXID = @AccountXID;

that will run if there's a record or more from this SELECT syntax for table Trx :

--Select for table Trx 
    select * from Trx  where  TimeRqTimestamp > DATEADD(HOUR, -1, GETDATE()) 
    and trxresp in (51,55)

I'll make this script for an event that will run every one hour in database.

If I run this SELECT syntax and there's one record, then Balance will update once with amount 2000 for accountxid 123, there's 2 record then the balance will update once AGAIN with amount 2000 for accountxid 123. And it'll update again if there's 3 record and more. It depend how many record that shows if the event run at that hour.

Example = Balance in AccountXID 123 on table Deposit 20.000. Then Event run and there's 4 record in table Trx in 1 last hour when the event run, it means 4 times update from 20.000 (20.000 - 2.000 - 2.0000 - 2.000 - 2.000 = 12.000) So, now record on Balance now 12.000

My desired output is table Deposit will update based on record everytime event run for select in table Trx. Table Trx shows 1 record, Table update will update once, Table Trx shows 2 record, table Deposit will update twice.

(sorry for broken english)

Edited :

I try to make the script based on the desired output, in this case I'm using count(*) from every record from table Trx, I don't know the result is right or not. the result like this :

begin
    declare @ctrx int;
    declare @Amount = 2000;
    declare @Accountxid = 123;
    
    set @ctrx = (select count(*) from Trx   
                    where 
                    TimeRqTimestamp > DATEADD(HOUR, -1, GETDATE())
                    and trxresp in (51,55);
                    
    update Deposit set Balance = Balance - (@Amount * @ctrx) where AccountXID = @AccountXID;
end;

highlytrainedbadger · Accepted Answer · 2020-09-14T10:18:02.700

0

You should clearly explain your desired output, information about the tables you mention (such as its columns, records, etc.), and not explain your queries with example parameters.

You can check out update code below that updates table Deposit based on records of Trx. You can use COUNT aggregation function to count the record amount and then update the balance.

@Amount = 2000
@Accountxid = 123

WITH Account_Deposit (AccountXID, Balance)
AS
(
SELECT
    AccountXID,
    COUNT(*) AS Record_Amount
FROM
    Trx
GROUP BY
    AccountXID
)
UPDATE
    Deposit
SET
    Balance = Balance - (Account_Deposit.Record_amount*@Amount)
FROM
    Deposit
INNER JOIN
    Account_Deposit
ON
    Deposit.AccountXID = Account_Deposit.AccountXID

If you only want to update account id '123' then add your clause below

WHERE AccountXID = @AccountXID

edited Sep 14 '20 at 10:18

answered Sep 14 '20 at 09:24

highlytrainedbadger

146
10

I just edited my thread based on you ask (Information about table and desired output) , and I try to make one script with my own logic on the thread too. Can you review it? – ilham setiawan Sep 14 '20 at 09:43
@ilhamsetiawan Your approach is correct if you want to update only one account id, you can try it. Please do not forget to mark my answer as correct if it is successful. – highlytrainedbadger Sep 14 '20 at 09:48

Update record with case statement for Sybase ASA11

1 Answers1