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, Jan 06 '21 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, Jan 06 '21 at 10:35
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) You need to filter with ` < purchase_date` — Charlieface, Jan 06 '21 at 12:13

score 1 · Answer 1 · answered Jan 06 '21 at 12:13

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).

eriksv88 · Answer 2 · 2021-01-06T13:00:11.647

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.

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

2 Answers2