Not able to use a select query to get number of days in DATEADD function

Question

I have a table, Table1, in the following format:

Country    No_Of_Days
  XX          5
  YY          10

And I need to update End_Date column in another table, Table2, which has a Start_Date by adding the days in above mentioned table to Start_date:

Country     Start_Date      End_Date    Calc_Manual_Flag
  XX        25-Dec-16       30-Dec-16        CALC
  YY        02-Jan-17       12-Jan-17        CALC  

I used the following query but it does not seem to work:

UPDATE dbo.Table2
SET End_date = 
dateadd(
        day,
        (SELECT No_Of_Days FROM Table1 WHERE Table2.Country = Table1.Country),Start_Date),
    Calc_Manual_Flag = 'CALC'
WHERE Table2.End_Date IS NULL
AND Table2.Start_Date IS NOT NULL

But it does not seem to be working properly. The updates do not seem to be happening as expected. I am getting the following error message:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery     follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Kindly provide guidance on this.

Answer 1

I would do this query using an explicit JOIN:

UPDATE t2
    SET End_date = DATEADD(day, t1.No_Of_Days, t2.Start_Date)
        Calc_Manual_Flag = 'CALC'
    FROM dbo.Table2 t2 JOIN
         Table1 t1
         ON t2.Country = t1.Country
    WHERE t2.End_Date IS NULL AND t2.Start_Date IS NOT NULL;

You can use a LEFT JOIN if you really want to update rows where there is no match.

This fixes the proximal cause of the problem, which is due to multiple matching rows in Table11. An arbitrary match is used for the update.

To really fix the problem, you need to decide what to do. If you want to subtract all matching days, then aggregate before joining:

UPDATE t2
    SET End_date = DATEADD(day, t1.No_Of_Days, t2.Start_Date)
        Calc_Manual_Flag = 'CALC'
    FROM dbo.Table2 t2 JOIN
         (SELECT Country, SUM(No_Of_Days) as No_Of_Days
          FROM Table1 t1
          GROUP BY Country
         ) t1
         ON t2.Country = t1.Country
    WHERE t2.End_Date IS NULL AND t2.Start_Date IS NOT NULL;