Professional Web Applications Themes

Sub-queries kludge - Microsoft SQL / MS SQL Server

OK, here's my problem - I want to update a 'main' table from an 'updates' table, they have a common field which I use for a join - making the query of the format "UPDATE maintable set maintable.latestupdate = updates.update FROM maintable join updates on maintable.field1 = updates.field1" - i.e. using update to specify the field in maintable to be updated, and the FROM clause to specify the join to the updates table where the update information is got from. Now, each record to be updated in maintable may have more than one record in updates - i.e. there may ...

  1. #1

    Default Sub-queries kludge

    OK, here's my problem - I want to update a 'main' table
    from an 'updates' table, they have a common field which I
    use for a join - making the query of the format "UPDATE
    maintable set maintable.latestupdate = updates.update FROM
    maintable join updates on maintable.field1 =
    updates.field1" - i.e. using update to specify the field
    in maintable to be updated, and the FROM clause to specify
    the join to the updates table where the update information
    is got from.
    Now, each record to be updated in maintable may have more
    than one record in updates - i.e. there may have been
    several updates to one particular agency over time (an
    agency is the entity of maintable), and conveniently, the
    update records have a week number field. For each record,
    I thus want to apply the most recent update last, so it is
    the one that takes final effect. e.g. If an agency was
    updated on weeks 2334, 2152 and 2205, I want the 2334
    update to be the last one, i.e. the one that 'stays'.
    Sorting the updates table into a temporary table first and
    updating from that achieves the objective, however if I
    try to put
    "UPDATE maintable
    set maintable.lastupdate = sq.update
    FROM (SELECT * FROM updates ORDER BY updateweek) sq JOIN
    maintable on sq.field1 = maintable.field1"
    it tells me I can't have order by in a sub query unless
    it's got TOP.. specified. So I change it to
    "UPDATE maintable
    set maintable.lastupdate = sq.update
    FROM (SELECT TOP 100 PERCENT * FROM updates ORDER BY
    updateweek) sq JOIN maintable on sq.field1 =
    maintable.field1"

    this kludge works fine, and achieves the objective of
    sorting the table before scanning it for updates! So why
    doesn't the compiler do this automatically, does it just
    assume that there's no way you'd want to?
    Ben Taylor Guest

  2. #2

    Default Re: Sub-queries kludge

    Ben,

    What you are donig may have inconsistent behavior. A safer way to
    do what you want is something like this - be specific that you only
    want to use the latest week for the update.

    update maintable set
    lastupdate = sq.update
    from updates sq
    where sq.field1 = maintable.field1
    and not exists (
    select * from updates sq2
    where sq2.field1 = maintable.field1
    and sq2.weeknumber > sq1.weeknumber
    )

    [untested]

    Steve Kass
    Drew University

    Ben Taylor wrote:
    >OK, here's my problem - I want to update a 'main' table
    >from an 'updates' table, they have a common field which I
    >use for a join - making the query of the format "UPDATE
    >maintable set maintable.latestupdate = updates.update FROM
    >maintable join updates on maintable.field1 =
    >updates.field1" - i.e. using update to specify the field
    >in maintable to be updated, and the FROM clause to specify
    >the join to the updates table where the update information
    >is got from.
    >Now, each record to be updated in maintable may have more
    >than one record in updates - i.e. there may have been
    >several updates to one particular agency over time (an
    >agency is the entity of maintable), and conveniently, the
    >update records have a week number field. For each record,
    >I thus want to apply the most recent update last, so it is
    >the one that takes final effect. e.g. If an agency was
    >updated on weeks 2334, 2152 and 2205, I want the 2334
    >update to be the last one, i.e. the one that 'stays'.
    >Sorting the updates table into a temporary table first and
    >updating from that achieves the objective, however if I
    >try to put
    >"UPDATE maintable
    >set maintable.lastupdate = sq.update
    >FROM (SELECT * FROM updates ORDER BY updateweek) sq JOIN
    >maintable on sq.field1 = maintable.field1"
    >it tells me I can't have order by in a sub query unless
    >it's got TOP.. specified. So I change it to
    >"UPDATE maintable
    >set maintable.lastupdate = sq.update
    >FROM (SELECT TOP 100 PERCENT * FROM updates ORDER BY
    >updateweek) sq JOIN maintable on sq.field1 =
    >maintable.field1"
    >
    >this kludge works fine, and achieves the objective of
    >sorting the table before scanning it for updates! So why
    >doesn't the compiler do this automatically, does it just
    >assume that there's no way you'd want to?
    >
    >
    Steve Kass Guest

Similar Threads

  1. Can't run queries
    By athanasiusrc in forum Coldfusion Database Access
    Replies: 14
    Last Post: July 25th, 09:35 PM
  2. Queries
    By free23 in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: May 18th, 03:01 PM
  3. Queries of Queries
    By slanza in forum Macromedia ColdFusion
    Replies: 1
    Last Post: June 3rd, 06:38 PM
  4. Queries Of Queries Single Quote Problem
    By TimH2O in forum Macromedia ColdFusion
    Replies: 0
    Last Post: April 1st, 07:46 PM
  5. ASP.net Queries
    By Srinivasa Raghavan in forum ASP.NET General
    Replies: 0
    Last Post: June 27th, 04:17 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139