locked
problem with alias in update statement RRS feed

  • Question

  • hi all,

    thanks for your help and support

    I have two table with same columns one table is holding daily data (customerprofile) and other table is holding the monthly data (
    Monthlycustomerprofile ) . so i want to add the column values if the month and year matches

    But it's not executing .

    I have pasted the query and the error


    Update   Monthlycustomerprofile a  set a.Handset=a.callrates + b.callrates
     From customerprofile b
     where mobilenumber=b.mobilenumber
    and datepart(month,a.signaturedate)=datepart(month,b.signaturedate)
    And datepart(year,a.signaturedate)=datepart(year,b.signaturedate)




    error shown is pasted below



    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'a'.


    if anybody can pointout the error please help me


    thanks & regards
    nevviin

    Monday, October 13, 2008 10:37 AM

Answers

  •  

    try this

    Code Snippet

    Update a

    set a.Handset=a.callrates + b.callrates

    From customerprofile b join Monthlycustomerprofile a

    on a.mobilenumber=b.mobilenumber

    where datepart(month,a.signaturedate)=datepart(month,b.signaturedate)

    And datepart(year,a.signaturedate)=datepart(year,b.signaturedate)

     

     

    Monday, October 13, 2008 10:42 AM

All replies

  •  

    try this

    Code Snippet

    Update a

    set a.Handset=a.callrates + b.callrates

    From customerprofile b join Monthlycustomerprofile a

    on a.mobilenumber=b.mobilenumber

    where datepart(month,a.signaturedate)=datepart(month,b.signaturedate)

    And datepart(year,a.signaturedate)=datepart(year,b.signaturedate)

     

     

    Monday, October 13, 2008 10:42 AM
  • nevviin,

     

    There is a problem with the alias. However, there is a larger problem with the query. The update statement has an implicit transaction. It also basis its update on a record as it looked before the update ran. Therefore, you update statement will update a record with the callrates values plus the callrate value from one record in the daily table (the last record it uses of the update. To get this to work you will have to create an aggregation query on the daily.

     

    For the purpose of this example I will assume that the monthly table holds the date as the first day of the month.

     

     

    Code Snippet

    UPDATE   a  

    SET

    a.Handset = a.callrates + b.callrates

    FROM 

    Monthlycustomerprofile a

    INNER JOIN

    (SELECT

    mobilenumber,

    DATEADD(MM, DATEDIFF(MM, 0, signaturedate, 0) AS signaturedate,

    SUM(callrates) AS callrates

    FROM

    customerprofile

    GROUP BY

    mobilenumber,

    DATEADD(MM, DATEDIFF(MM, 0, signaturedate, 0)

    ) b

    ON a.mobilenumber = b.mobilenumber
    and a.signaturedate = .signaturedate

     

     

    The added advantage of this solution is that if there is an index on signature date of the monthly table then it can be considered for the purpose of carrying out the join. Whereas, the use of the year and month functions cannot use indexes as they prevent the where clause from forming SARGs (searchable arguments).

     

    Rick

     

     

    Monday, October 13, 2008 11:02 AM