How to use while loop in SQL Server so as to update a table with values from another table

Question

I have 2 tables tbl_salesData and tbl_purchasedata. I want to get the recent purchase price from table tbl_purchasedata into tbl_salesData based on the col_salesdate column. The purchase price must be before the sales date.

I need purchase price based on sales date as recent purchase date.

ex. for item Mouse the sales date is 24-07-2020 and the recent purchase price will be just before the sales date i.e, 22-07-2020 so the purchase price will be 250. The output table is like this below.

I tried but this didn't work as I am new to stored procedures and other things.

Declare @total_Count int 
Declare @row_num int
set @total_Count = (select Count(*) from vw_Final_Data)

set @row_num = 1

while @total_Count < @row_num
begin
    /****Some code here*****/
end

I want this to be created as stored procedure and run using scheduler.

Please help!

Using a WHILE, in SQL, should (almost) always be your last choice. SQL is a set based language, and thus is performs awfully at iterative operations (like a WHILE). If you want to use a loop in SQL, you almost certainly are making the wrong design choice. (There are rare exceptions to this rule, such as when doing bulk operations, this doesn't look to be one of them.) — Thom A
– Thom A ♦, Commented Jan 6, 2021 at 10:33
Images of data don't really help us help you; please don't expect us to transcribe your data. Take the time to post your data in a consumable format as well; preferable DDL and DML statement however well formatted tabular text (please don't use the new table markdown, it's not great for copying) is also far more helpful than an image of text. — Thom A
– Thom A ♦, Commented Jan 6, 2021 at 10:35
Does this answer your question? Get top 1 row of each group You need to filter with ` < purchase_date` — Charlieface
– Charlieface, Commented Jan 6, 2021 at 12:13

Gordon Linoff · Accepted Answer · 2021-01-06 12:13:36Z

1

You would not use a while loop for this. As mentioned in the comments, such loops are very rarely used.

Instead, SQL Server supports lateral joins -- apply in SQL Server syntax:

select sd.*, pd.*
from tbl_salesdata sd outer apply
     (select top (1) pd.col_purchase_date, pd.purchase_price
      from tbl_purchasedata pd
      where pd.item = sd.item and
            pd.col_purchase_date < sd.col_sales_date
      order by pd.col_purchase_date desc
     ) pd;

Use <= if "before" really means "on or before".

For performance, you want an index on tbl_purchasedata(item, col_purchase_date).

answered Jan 6, 2021 at 12:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

eriksv88 · Accepted Answer · 2021-01-06 13:00:11Z

1

UPDATE S
SET Purchase_Price=(SELECT TOP 1 Purchase_Price 
                        FROM tbl_purchaseData 
                        WHERE Items=S.Items AND col_purchase_date<S.col_sales_date
                        ORDER BY Purchase_Price ASC)
FROM tbl_salesdata AS S

Try to avoid using cursor or while. It is very demanding for SQL Server. And requires a lot of resources.

Consider making a trigger on tbl_purchasedata.

edited Jan 6, 2021 at 13:00

answered Jan 6, 2021 at 12:39

eriksv88

3,5463 gold badges35 silver badges51 bronze badges

Collectives™ on Stack Overflow

How to use while loop in SQL Server so as to update a table with values from another table

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related