how to update the multiple row based on select value in MSSQL

Question

First I have insert the data from bb_IssueReq table to @transaction , after that i want update the multiple row based on selected SQL query trans_id . Now the result only can update the single row. do you have any suggestion to improve the SQL query for update the multiple row in @transaction. The SQL query was attached below.

DECLARE @transaction table (
        id bigint,invoice_no varchar(50),[time] datetime,
        trans_id bigint,cust_hp varchar(50),
        cardno varchar(20),username varchar(200),
        bb_value money,bb_amount money,
        key_amt money, charge money,
        bb_type int,pay_amt money
    )

    INSERT INTO @transaction
       (id, invoice_no, [time], trans_id, cust_hp, cardno, username, 
            bb_value, bb_amount, key_amt, charge)

        SELECT top 20
            id as issue_idx,
            invoice_no as [invoice_no],
            Updated as [timestamp],
            id as trans_id,
            hp as cust_hp,
            card_no as cardno,
            name as username,
            b_value as bb_value,
            amount as bb_amount,
            Amount as key_amt,
            charr as charge,
        FROM bb_IssueReq(NOLOCK)
        where status = 0 and id ='1211'

        declare @trans bigint, @bbtype int, @payout money

        SELECT top 20
            @trans =A.trans_id,
            @bbype =B.type,
            @payout =Cast(C.amt * C.rate) 
        FROM bb_IssueReq(NOLOCK) A
        inner join vw_req B on A.trans_id = B.id 
        inner join trant C on C.payroll_tranid = B.redeem_id
        where A.status = 0 and A.id = '1211'

            ********* update query  based on @trans *********

        update @transaction
        set bb_type = @bbtype,
            pay_amt = @payout
        where  trans_id = @trans 

        select * from @transaction

The first thing to do is to STOP splattering your code with [nolock](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere). And if you do use it (can't possibly need it IMO) then at least use the current syntax and not the deprecated syntax. — SMor, Jul 15 '21 at 12:16
Whitespace doesn't cost, you know. And `NOLOCK` is not a "go-faster" switch, it's a "give-incorrect-results" switch. It's unclear why you need the variables and table variables anyway, the whole thing could be one big joined `update` — Charlieface, Jul 15 '21 at 13:15
Does this answer your question? [How can I do an UPDATE statement with JOIN in SQL Server?](https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql-server) — Charlieface, Jul 15 '21 at 13:16

score 2 · Answer 1 · answered Jul 15 '21 at 12:16

It would be the same as any other UPDATE of a join

UPDATE d
SET d.columnInDest = s.columnInSource, ...
FROM
  destinationTable d JOIN sourceTable s ON ...
WHERE
  ...

The join co-ordinates the rows between your @transaction table (and it is a table, effectively) and your other table, then the update specifies how the data is copied. It can be as complex as any other update of a single table (you can update d with the results of sums performed on s's columns etc)

I'm not really sure which table is your source and which is your destination, hence the generic advice

how to update the multiple row based on select value in MSSQL

1 Answers1