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