Click here to Skip to main content
15,444,203 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have two tables in SQLite database. One is the product stock table and another is the sell table. When I sell a product stock table should be updated with new value.
Example:

 stock_table
|------------|--------|-----|
|product_code|Name    |stock|
|------------|--------|-----|
|101         |Mobile  |50   |
|102         |Laptop  |50   |
|103         |Monitor |30   |  
|------------|--------|-----|

  sell_table
|------------|--------|--------|
|product_code|Name    |sell_qty|
|------------|--------|--------|
|101         |Mobile  |2       |
|102         |Laptop  |3       |
|103         |Monitor |5       |  
|101         |Mobile  |4       |
|103         |Laptop  |2       |
|101         |Mobile  |4       |
|------------|--------|--------|

I want this result.
 stock_table
|------------|--------|-----|
|product_code|Name    |stock|
|------------|--------|-----|
|101         |Mobile  |40   |
|102         |Laptop  |47   |
|103         |Monitor |23   |  
|------------|--------|-----|


What I have tried:

public void stockUpdate(String code) {
        SQLiteDatabase db = this.getWritableDatabase();

       db.execSQL(" UPDATE STORE_PRODUCT_DATABASE SET STOCK = STOCK - (SELECT SELL_QTY FROM STORE_SELL_DATABASE SSD WHERE PRODUCT_CODE = SSD.PRODUCT_CODE )" + " WHERE PRODUCT_CODE = ? ",new String[] {code});
}


I get my desired result but want to know is there any best way to do this. Or is there any wrong with my code? Should I use raw query? Help pls.
Posted
Updated 25-Oct-21 4:24am

1 solution

When I run your code for product 101 I get an error reported
Quote:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
That is because you have multiple sales for that product, but no date or time criteria - and no way to identify "the most recent record". You either need to be able to define exactly which sales record you want to adjust the stock by OR just do them all at once as a set. Either way you are probably going to need a datetime column in your table.

You then want to sum all the relevant records by Product Code. E.g.
SQL
declare @stock table (product_code int, [name] varchar(50), stock int)
insert into @stock (product_code, [name],stock) values
(101,'Mobile',50),
(102,'Laptop',50),
(103,'Monitor',30);

declare @sell_table table (product_code int, [name] varchar(50), sell_qty int)
insert into @sell_table (product_code, [name],sell_qty) values
(101,'Mobile',  2),
(102,'Laptop',  3),
(103,'Monitor', 5),
(101,'Mobile' , 4),
(103,'Laptop' , 2),
(101,'Mobile' , 4);

update a set a.stock = a.stock - b.sell_qty
from @stock a
inner join (select product_code, sum(sell_qty) as sell_qty from @sell_table group by product_code) b on a.product_code = b.product_code

select * from @stock


EDIT after OP comment:
Scenario 1 : You have a job that runs at the end of the day that calculates the end-of-day stock situation. So you want to total the amounts by product for the entire day
SQL
update a set a.stock = a.stock - b.sell_qty
from @stock a
inner join (
	select product_code, sum(sell_qty) as sell_qty 
	from @sell_table 
	where [date] between @startOfDay  and  @endOfDay
	group by product_code) b on a.product_code = b.product_code
But that tends to problems - you have to schedule the job, make sure it is not run twice etc.

Scenario 2 : You update the stock at the time of updating the sale.
SQL
BEGIN TRANSACTION Sale
insert into @sell_table (product_code, [name],sell_qty,[date]) values
(101,'Mobile',  2,'2021-10-27 06:59:36');
update @stock set stock = stock - 2 where product_code = 101;
COMMIT Sale;
But for that you should get into transactions etc.

Scenario 3: What I think this exercise is really about - Running Totals using Window Functions[^] e.g. something like this
SQL
select s.product_code, s.[name], sell_qty, [date], stock, stock - sum(sell_qty) over (partition by s.product_code order by [date])
from @sell_table st
join @stock s on st.product_code = s.product_code
order by [date]
As an aside, looking at this again I need to advise you that you should not have [name] in both stock and sell tables. That duplication is not necessary and does not follow Database normalization[^] principles
 
Share this answer
 
v2
Comments
Sadikur Rahman 27-Oct-21 3:10am     CRLF
so if I have a timestamp like the below table then how I will do subtract from stock table and get the result.
 sell_table|------------|--------|--------|-----------------------||product_code|Name    |sell_qty|date                   ||------------|--------|--------|-----------------------||101         |Mobile  |2       |27-10-2021 06:59:36 GMT||102         |Laptop  |3       |27-10-2021 07:59:36 GMT||103         |Monitor |5       |27-10-2021 08:59:36 GMT|  |101         |Mobile  |4       |27-10-2021 09:59:36 GMT||103         |Laptop  |2       |27-10-2021 10:59:36 GMT||101         |Mobile  |4       |27-10-2021 11:59:36 GMT||------------|--------|--------|-----------------------|
please help

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900