0

I have a SQL Server 2019 table of stock price data for multiple stock symbols (daily_price). Each row represents 1 day of data for a specific symbol_id.

For each symbol_id, I want to set the 'rs_delta' based on an 'rs_avg_rank' from the row pointed at by a 'price_date' from either the 'c3_date' or 'c2_date' column. The value of 'cont_stage' determines which column is used to find the 'price_date'.

Here's my attempt to describe the desired behavior in SQL, but I can't figure out the correct syntax:

    SELECT symbol_id, rs_avg_rank FROM daily_price p
JOIN 
(UPDATE
daily_price 
SET rs_delta = 
    CASE 
    WHEN
    cont_stage = 'C3'
    then 
     rs_avg_rank-(SELECT rs_avg_rank FROM daily_price d WHERE d.price_date = p.c2_date) 
    WHEN
    cont_stage = 'C4'
    then 
    rs_avg_rank-(SELECT rs_avg_rank FROM daily_price d WHERE d.price_date = p.c3_date) 
    END
) newp
ON p.symbol_id = newp.symbol_id
WHERE cont_stage>='C3'

Thanks!

Kurt
  • 1
  • 1
  • Seems you need the syntax `UPDATE ... SET ... FROM ... JOIN...`; like in [this example](https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver16#l-specifying-a-table-alias-as-the-target-object) – Thom A Jun 27 '22 at 15:55
  • thanks! that example showed me the correct syntax to get it working. – Kurt Jun 27 '22 at 17:18

0 Answers0