locked
Problem with sql update statement RRS feed

  • Question

  • Hi

    I have the following update statement. Then problem is that I need to use the exchangerate found on the filteredaccount view and not the exchangerate from the filteredcit_budget. How do I do that?

     

    update filteredaccount

    set

    cit_linakdynamicbudget = (select sum(cit_dynamicbudget_base * exchangerate) FROM filteredcit_budget)

    Tuesday, February 1, 2011 10:31 AM

Answers

All replies

  • Sorry cannot test it right now

     

    update filteredaccount

    set

    cit_linakdynamicbudget = (select sum(cit_dynamicbudget_base * exchangerate) FROM filteredcit_budget WHERE filteredaccount.ID=filteredcit_budget.ID)

    WHERE EXISTS (SELECT * FROM filteredcit_budget WHERE filteredaccount.ID=filteredcit_budget.ID)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, February 1, 2011 10:41 AM
    Answerer
  • Hi Christian Dollerup !

    I didn’t see anything wrong with the query this query works fine when I executed it. Just one thing to add you are updating without having ‘WHERE’ clause in your query.

    Can you please share the error message which you are getting while executing the statement.

    Thanks, Hasham

    Tuesday, February 1, 2011 10:43 AM
    Answerer
  • You can use a join in the set statement without a problem:

     

    update filteredaccount

    set

    cit_linakdynamicbudget = ( select sum ( fb.cit_dynamicbudget_base * f.exchangerate) FROM filteredcit_budget fb inner join filteredaccount f on f.id = fd.id)

     

    The query between the parenthesis is the same as any normal query. You just get a result back, in this case you can only return 1 column of course.

    • Proposed as answer by dents Wednesday, February 2, 2011 4:48 AM
    • Marked as answer by Kalman Toth Monday, February 7, 2011 8:12 AM
    • Unmarked as answer by Kalman Toth Tuesday, February 8, 2011 6:34 AM
    Tuesday, February 1, 2011 10:46 AM
  • In addition to the above suggestions the following article deals with the UPDATE syntax:

    http://www.sqlusa.com/articles2005/sqlupdate/

     


    Kalman Toth, Business Intelligence Developer; SQL 2008 GRAND SLAM
    • Marked as answer by KJian_ Wednesday, February 9, 2011 8:53 AM
    Monday, February 7, 2011 8:12 AM