How to update a table using a subquery that returns multiple rows in SQL Server?

Question

I'm trying to update the Table LIQUIDITYACCOUNT using the field QUANTITY which belongs to the table STOCKACCOUNT. Both tables have foreign key CLIENT_ID.

update
    lq 
set
    lq.AMOUNT = lq.AMOUNT + ( st.QUANTITY % @num) * @rompu 
from
    LIQUIDITYACCOUNT lq
    inner join STOCKACCOUNT st on
        lq.CLIENT_ID = st.CLIENT_ID
        and
        st.TITRE_ID = @id_titre
        and
        st.QUANTITY > 0

The table STOCKACCOUNT contains informations about STOCKS hold by a customer while the table LIQUIDITYACCOUNT contains information about cash money. If the result of the subquery contains one row, it work. But it doesn't for multiple rows. And in this generally the case in my project since any customer can hold many shares of different stocks.

How can I make it work when the subquery returns multiple rows.

If it returns multiple rows, which one would you want doing the update? That's a business logic question, and there's not enough information in this question for someone to answer it. — tysonwright
– tysonwright, Commented Mar 14, 2018 at 23:05
Thanks, let's say that every row return by the subquery should be updated using the formula *lq.AMOUNT = lq.AMOUNT + ( st.QUANTITY % @num) * @rompu * — Mahamane Sani Ibrahim
– Mahamane Sani Ibrahim, Commented Mar 14, 2018 at 23:16

paparazzo · Accepted Answer · 2018-03-15 13:10:21Z

1

Purpose of the update seems odd but here it is

update lq 
   set lq.AMOUNT = lq.AMOUNT + stt.qty * @rompu  
  from LIQUIDITYACCOUNT lq
  join ( select st.CLIENT_ID, sum(st.QUANTITY % @num) as qty
           from STOCKACCOUNT st 
          where st.TITRE_ID = @id_titre 
            and st.QUANTITY > 0 
           group 
              by st.CLIENT_ID 
       ) stt
     on stt.CLIENT_ID = lq.CLIENT_ID

edited Mar 15, 2018 at 13:10

answered Mar 15, 2018 at 0:22

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

paparazzo Over a year ago

@MahamaneSaniIbrahim I am sorry but not working does not give my much to go on.

Mahamane Sani Ibrahim Over a year ago

Is it possible to perform it using 'while' loop ?

paparazzo Over a year ago

@MahamaneSaniIbrahim Not buying my update is throwing that error. Good luck.

tysonwright · Accepted Answer · 2018-03-14 23:27:56Z

0

So the primary key on your lq table can be pk. Then the SQL for updating lq.AMOUNT with this logic is:

UPDATE
    lq 
SET
    lq.AMOUNT = sq.newvalue
FROM
    (
    SELECT 
        lq.AMOUNT + ( st.QUANTITY % @num) * @rompu AS newvalue,
        lq.pk
    FROM
        LIQUIDITYACCOUNT lq
    INNER JOIN
        STOCKACCOUNT st 
    ON
        lq.CLIENT_ID = st.CLIENT_ID
    AND
        st.TITRE_ID = @id_titre
    AND
        st.QUANTITY > 0
    ) AS sq
WHERE
    lq.pk = sq.pk

answered Mar 14, 2018 at 23:27

tysonwright

1,5151 gold badge9 silver badges19 bronze badges

Comments

Mahamane Sani Ibrahim · Accepted Answer · 2018-03-15 21:18:02Z

@tysonwright, @paparazzo and @Dai thank you so much indeed. I have found a solution using cursor and while loop. Below is the code.

declare @id_client as varchar(50)
declare @amount as money

declare liq_cursor cursor for

SELECT 
    lq.AMOUNT + ( st.QUANTITY % @num) * @rompu AS newvalue,
    lq.CLIENT_ID
FROM
    LIQUIDITYACCOUNT lq
INNER JOIN
    STOCKACCOUNT st 
ON
    lq.CLIENT_ID = st.CLIENT_ID
AND
    st.TITRE_ID = @id_titre
AND
    st.QUANTITY > 0



OPEN liq_cursor
    FETCH NEXT  FROM liq_cursor  into @amount,@id_client
 ---

 WHILE @@FETCH_STATUS=0
 BEGIN
        update LIQUIDITYACCOUNT set AMOUNT=@amount,
                                    DATEMODIF=GETDATE()
        where CLIENT_ID=@id_client
        FETCH NEXT  FROM liq_cursor  into @amount,@id_client
 END

 close liq_cursor
 deallocate liq_cursor

Collectives™ on Stack Overflow

How to update a table using a subquery that returns multiple rows in SQL Server?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related