Need SQL query to update the column using case when by joining two tables-CodePudding

There are 2 tables : Sales & Transaction. I need to update the INDICATOR column of the Sales table using CASE WHEN by joining these two tables.

Using below query but not working :

Update Sales a set 
a.indicator= 
CASE WHEN  a.retail_value >= t.selling_price then 'Y'
     WHEN  a.retail_value <  t.selling_price then 'N'
     ELSE NULL 
     END    
     FROM Transaction t 
     WHERE a.id = t.id and a.date <= t.date;

CodePudding user response：

I think you forgot to mention the a.indicator column after the CASE keyword. I feel the below query might work

 Update Sales a set 
 a.indicator = CASE a.indicator
 WHEN  a.retail_value >= t.selling_price then 'Y'
 WHEN  a.retail_value <  t.selling_price then 'N'
 ELSE NULL 
 END    
 FROM Transaction t 
 WHERE a.id = t.id and a.date <= t.date;

CodePudding user response：

Try like this;

UPDATE a
SET a.indicator = CASE
                      WHEN  a.retail_value >= t.selling_price THEN 'Y'
                      ELSE 'N'
                  END
FROM Sales AS a
JOIN Transaction AS t
ON a.id = t.id
WHERE a.date <= t.date;