Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007

    Unanswered: update multiple rows


    I want to update the values of one column in multiple rows of a table. My query below generates an error saying that the subquery returns multiple rows and this is not allowed:

    CREATE TABLE #tempAS_trans ( ticket char(8), isin char(12), strategy varchar(10), face money, ratecode tinyint null, rate float null )

    update #tempAS_trans
    set rate = (select rr.rate
    from tableA rr, #tempAS_trans tt
    where rr.ticket in (select ticket from #tempAS_trans)
    and rr.ticket = tt.ticket
    and rr.when_date <= @as_of_date
    group by rr.ticket
    having max(rr.when_date) = rr.when_date)
    where EXISTS (select distinct rr.ticket from tableA rr where rr.ticket =

    Is there a way that I can get it to update multiple rows and have the nested query return only one row. I'm assuming I need a way of telling the subquery which row is currently being looked at? Or something similar?

    Can anybody help with my problem?


  2. #2
    Join Date
    May 2005
    South Africa
    Provided Answers: 1
    It is difficult to understand from the code what you are trying to do
    Can you show some sample data and the expected result
    Why do you use a temp table, you probably don't need it

    update #tempAS_trans
    set rate = rr.rate
    from tableA rr, #tempAS_trans tt
    where rr.ticket = tt.ticket
    and rr.when_date=
    (select min(mm.when_date)
    from tableA mm
    where mm.ticket = rr.ticket
    and mm.when_date <= @as_of_date)

  3. #3
    Join Date
    Oct 2007
    I'll try to give some sample date to help explain the problem.

    ticket isin strategy face ratecode rate
    1a a123 ab 100 1 null
    1b a234 ab 150 1 null
    1c a567 fe 110 1 null

    ticket when_date rate
    1a 13/10/2007 0.1
    1a 18/10/2007 0.2
    1a 25/10/2007 0.25
    1b 14/10/2007 0.15
    1b 20/10/2007 0.3
    1c 15/10/2007 0.22


    So in the situation above I want to update #tempAS_trans table with the appropriate rate from tableA. If @as_of_date is 19/10/2007 then the rate selected for 1a should be 0.2, for 1b the rate should be 0.15 and for 1c it should be 0.22.

    So what I need to do is select the rate from the row that has a when_date <= @as_of_date (so if there are 2 rows that meet this criteria I only want to select the most recent of the 2).

    I hope this clears a few things up.


  4. #4
    Join Date
    Oct 2007
    The query below seems to be working OK for me...

    update #tempAS_trans
    set rate = (
    select rr.rate --select rr.ticket, rr.rate, rr.when_date
    from RepoRate rr, #tempAS_trans tt
    where rr.ticket = tt.ticket
    and rr.when_date = (select max(mm.when_date)
    from RepoRate mm
    where mm.ticket = rr.ticket
    and mm.when_date <= @as_of_date)
    and rr.ticket = #tempAS_trans.ticket

    I was able to leave out the where EXISTS... part of the query.

    If you can see any problems that may arise with this please let me know.
    Thanks for your help so far.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts