Update query using inner join

Question

Why I get the following syntax error in this section of stored procedure :

Incorrect syntax near the keyword 'FROM'.

WITH CTE_Residence_Overtime
    AS
    (
      SELECT * 
      FROM #Residence
    )
    UPDATE t1 
    SET t1.over_time = t1.over_time + CONVERT(TIME, CAST(CTE_Residence_Overtime.overtimeHours AS VARCHAR(2))  
    FROM r_overtime AS t1
    INNER JOIN CTE_Residence_Overtime 
    ON t1.[trans_date] = CTE_Residence_Overtime.[dayDate];

Looks like you are missing a closing parentheses on that CONVERT() — JNevill
– JNevill, Commented May 20, 2015 at 15:11

Aahana · Accepted Answer · 2015-05-20 15:13:39Z

5

I think you are missing one bracket

WITH CTE_Residence_Overtime
    AS
    (
      SELECT * 
      FROM #Residence
    )
    UPDATE t1 
    SET t1.over_time = t1.over_time + CONVERT(TIME, CAST(CTE_Residence_Overtime.overtimeHours AS VARCHAR(2)))  
    FROM r_overtime AS t1
    INNER JOIN CTE_Residence_Overtime 
    ON t1.[trans_date] = CTE_Residence_Overtime.[dayDate];

answered May 20, 2015 at 15:13

Aahana

662 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2015-05-20 15:13:07Z

3

You are missing a closing paren on the set:

    SET t1.over_time = t1.over_time +
                       CONVERT(TIME,
                               CAST(CTE_Residence_Overtime.overtimeHours AS VARCHAR(2)
                                   )  
                              )
------------------------------^

answered May 20, 2015 at 15:13

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

FutbolFan · Accepted Answer · 2015-05-20 15:14:29Z

2

One missing parenthesis can make your life quite hectic!

WITH CTE_Residence_Overtime
        AS
        (
          SELECT * 
          FROM #Residence
        )
        UPDATE t1 
        SET t1.over_time = t1.over_time + CONVERT(TIME, CAST(CTE_Residence_Overtime.overtimeHours AS VARCHAR(2)))  
        FROM r_overtime AS t1
        INNER JOIN CTE_Residence_Overtime 
        ON t1.[trans_date] = CTE_Residence_Overtime.[dayDate];

answered May 20, 2015 at 15:14

FutbolFan

13.9k3 gold badges27 silver badges35 bronze badges

Collectives™ on Stack Overflow

Update query using inner join

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related