Update using case with multiple tables

Question

I have two tables. I want to update table1 when the condition is satisfied. The condition here is to check the country in table 2 and if its Mex, then multiply rate i.e. 0.5 to the price.

I wrote the following code

UPDATE table1
SET table1.Price = (SELECT *,
                           CASE table2.Country
                                WHEN 'CANADA' THEN (1 * table2.price)
                                WHEN 'MEXICO' THEN (0.5 * table2.price)
                                ELSE 0
                           END AS Price_Calc
                    FROM table2)
FROM table1;

As I run this it gives the below error

Msg 116, Level 16, State 1, Line 12
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

What about the error don't you understand here? The error is telling you exactly what the problem is; your sub query is returning multiple column (and likely rows). What result would you be expecting `Price` to be set to when you're trying to assign it the value of **every** column and row in the table `table2` and the value of expression for `Price_calc`? — Thom A, May 14 '19 at 12:51
You need to do a join and use case on your join. Create select first to test and see results, then change to update. — Brad, May 14 '19 at 12:52
Possible duplicate of [Only one expression can be specified in the select list when the subquery is not introduced with EXISTS](https://stackoverflow.com/questions/1904314/only-one-expression-can-be-specified-in-the-select-list-when-the-subquery-is-not) — atarasenko, May 14 '19 at 13:46

score 0 · Answer 1 · answered May 14 '19 at 13:11

Try Like below

UPDATE t1
SET t1.table1.Price = (SELECT
                           CASE t2.Country
                                WHEN 'CANADA' THEN (1 * t2.price)
                                WHEN 'MEXICO' THEN (0.5 * t2.price)
                                ELSE 0
                           END AS Price_Calc
                    FROM table2 t2
                    WHERE t2.Id = t1.Id -- Here it is suggestion to update target 
                                        -- column based on relation if exists between 
                                        -- ur  tables

                     )
FROM table1 t1;

score 0 · Answer 2 · answered May 15 '19 at 03:35

Assuming Table1 and Table2 are related through IDs:

UPDATE t1 SET t1.Price = t2.Price 
                    * CASE t2.Country 
                        WHEN 'CANADA' THEN 1. 
                        WHEN 'MEXICO' THEN .5
                        ELSE 0
                      END
FROM table1 t1
INNER JOIN table2 t2 ON t2.Id = t1.Id
;

Update using case with multiple tables

2 Answers2