Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update a row incrementally based on data from other table

Status
Not open for further replies.
kaht

kaht

Programmer
Aug 18, 2003
4,156
US
I have 2 tables which are linked by userid. There is a column in the first table that I need to update based on the way the table would be sorted using criteria from another table. For example:
Code: 
[COLOR=blue]set[/color] [COLOR=#FF00FF]nocount[/color] [COLOR=blue]on[/color]
[COLOR=blue]declare[/color] @a [COLOR=blue]table[/color] (userid [COLOR=blue]char[/color](3), [COLOR=#FF00FF]start[/color] [COLOR=blue]smalldatetime[/color], tiebreaker [COLOR=blue]tinyint[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'aaa'[/color], [COLOR=red]'2007-01-01'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'bbb'[/color], [COLOR=red]'2007-01-01'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'ccc'[/color], [COLOR=red]'2007-01-01'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'ddd'[/color], [COLOR=red]'2007-02-01'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'eee'[/color], [COLOR=red]'2007-02-01'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'fff'[/color], [COLOR=red]'2007-02-01'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'ggg'[/color], [COLOR=red]'2007-03-01'[/color], 0)

[COLOR=blue]declare[/color] @b [COLOR=blue]table[/color] (userid [COLOR=blue]char[/color](3), blah [COLOR=blue]smallint[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'aaa'[/color], 5)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'bbb'[/color], 10)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'ccc'[/color], -1)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'ddd'[/color], 69)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'eee'[/color], 1134)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'fff'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'ggg'[/color], 1)
[COLOR=blue]set[/color] [COLOR=#FF00FF]nocount[/color] [COLOR=blue]off[/color]

[COLOR=blue]select[/color] a.userid, a.start, a.tiebreaker, b.blah
[COLOR=blue]from[/color] @a a
   [COLOR=blue]inner[/color] [COLOR=blue]join[/color] @b b
   [COLOR=blue]on[/color] a.userid = b.userid
[COLOR=blue]order[/color] [COLOR=blue]by[/color] a.start, b.blah

In this example, the result set is sorted by the start column from @a, and then by the blah column from @b. The result from the select statement returns the data in the correct order. However, I need to populate the tiebreaker column in @a based on the way the result set would be sorted from the blah column in @b, and grouped by start in @a.

For example, the result set from the above query would look like this if the tiebreaker column was already populated:
Code: 
userid start                             tiebreaker blah   
------ --------------------------------- ---------- ------ 
ccc    2007-01-01 00:00:00               0          -1
aaa    2007-01-01 00:00:00               [!]1[/!]          5
bbb    2007-01-01 00:00:00               [!]2[/!]          10
fff    2007-02-01 00:00:00               0          0
ddd    2007-02-01 00:00:00               [!]1[/!]          69
eee    2007-02-01 00:00:00               [!]2[/!]          1134
ggg    2007-03-01 00:00:00               0          1

(7 row(s) affected)

Since ccc, aaa, and bbb all shared the same start date, they were assigned incrementing tiebreakers determined by the sort order from the blah column. The same with fff, ddd, and eee.

The data in the blah column will be somewhat unpredictable - so I couldn't think of an equation to perform on it to generate the tiebreaker. The only other way I could think to solve this problem was with a loop or a cursor. Is there a simpler and faster set-based query that will update the values correctly?

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
 
Sort by date Sort by votes
So, is this one of those rare occasions where a cursor or loop is necessary?

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
 
Upvote 0 Downvote
Sorry, SQL Server 2000

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
 
Upvote 0 Downvote
In that case it makes it a little more difficult. In SQL 2005, you could have used the RANK() function but in 2000, you have to use workaround by using a sub-query. There's an an example of this here but just shout if you want an example using your data.


------------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
 
Upvote 0 Downvote
Thanks a lot for the link Mark. That got me on the right track. I would have asked for an example using my data, but I wanted to figure it out myself [smile]

Here was the solution I came up with using your link:

Code: 
[COLOR=blue]set[/color] [COLOR=#FF00FF]nocount[/color] [COLOR=blue]on[/color]
[COLOR=blue]declare[/color] @a [COLOR=blue]table[/color] (userid [COLOR=blue]char[/color](3), [COLOR=#FF00FF]start[/color] [COLOR=blue]smalldatetime[/color], tiebreaker [COLOR=blue]smallint[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'aaa'[/color], [COLOR=red]'2007-01-01'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'bbb'[/color], [COLOR=red]'2007-01-01'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'ccc'[/color], [COLOR=red]'2007-01-01'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'ddd'[/color], [COLOR=red]'2007-02-01'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'eee'[/color], [COLOR=red]'2007-02-01'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'fff'[/color], [COLOR=red]'2007-02-01'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'ggg'[/color], [COLOR=red]'2007-03-01'[/color], 0)

[COLOR=blue]declare[/color] @b [COLOR=blue]table[/color] (userid [COLOR=blue]char[/color](3), blah [COLOR=blue]smallint[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'aaa'[/color], 5)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'bbb'[/color], 10)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'ccc'[/color], -1)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'ddd'[/color], 69)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'eee'[/color], 1134)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'fff'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'ggg'[/color], 1)
[COLOR=blue]set[/color] [COLOR=#FF00FF]nocount[/color] [COLOR=blue]off[/color]

[COLOR=blue]select[/color] a.userid, a.start, a.tiebreaker, b.blah
[COLOR=blue]from[/color] @a a
   [COLOR=blue]inner[/color] [COLOR=blue]join[/color] @b b
   [COLOR=blue]on[/color] a.userid = b.userid
[COLOR=blue]order[/color] [COLOR=blue]by[/color] a.start, b.blah

[COLOR=blue]update[/color] @a
[COLOR=blue]set[/color] tiebreaker = 
   (
      [COLOR=blue]select[/color] [COLOR=#FF00FF]count[/color](*)
      [COLOR=blue]from[/color] @a a
         [COLOR=blue]inner[/color] [COLOR=blue]join[/color] @b b
         [COLOR=blue]on[/color] a.userid = b.userid
      [COLOR=blue]where[/color] c.start = a.start
         and c.userid <> a.userid
         and b.blah < [COLOR=blue]d[/color].blah
   )
[COLOR=blue]from[/color] @a c
   [COLOR=blue]inner[/color] [COLOR=blue]join[/color] @b [COLOR=blue]d[/color]
   [COLOR=blue]on[/color] c.userid = [COLOR=blue]d[/color].userid



[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @a [COLOR=blue]order[/color] [COLOR=blue]by[/color] [COLOR=#FF00FF]start[/color], tiebreaker

With output of:
Code: 
userid start                       tiebreaker 
------ --------------------------- ---------- 
ccc    2007-01-01 00:00:00         0
aaa    2007-01-01 00:00:00         1
bbb    2007-01-01 00:00:00         2
fff    2007-02-01 00:00:00         0
ddd    2007-02-01 00:00:00         1
eee    2007-02-01 00:00:00         2
ggg    2007-03-01 00:00:00         0

(7 row(s) affected)

Thanks again for the help [thumbsup2]

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
 
Upvote 0 Downvote
For what it's worth, a correlated subquery as you've chosen could be the best performing.

But if performance matters (and with extremely large tables a correlated subquery is probably going to make it matter) you might want to look into using a derived table. Or the temp-table-with-identity-column insert method. Each has certain advantages and drawbacks.

There have been questions on this within the last week or two in this forum. I both posted in one that has examples of all three.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

The best part about anything that has cheese is the cheese.[/color]
 
Upvote 0 Downvote
ESquared, I used Mark's link to get it to work earlier. However, during my lunch break I figured I'd come back to the problem to solve it as you suggested - using a derived table. It wasn't a problem displaying the data, but upon trying the update I get the error "An aggregate may not appear in the set list of an UPDATE statement."

Before, I set tiebreaker = count(*) from the previous query. The only way I can envision duplicating that process is to set tiebreaker = sum(data from left join of derivedtable)

Here's what I've got, how do I make the update work?
Code: 
[COLOR=blue]set[/color] [COLOR=#FF00FF]nocount[/color] [COLOR=blue]on[/color]
[COLOR=blue]declare[/color] @a [COLOR=blue]table[/color] (userid [COLOR=blue]char[/color](3), [COLOR=#FF00FF]start[/color] [COLOR=blue]smalldatetime[/color], tiebreaker [COLOR=blue]smallint[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'aaa'[/color], [COLOR=red]'2007-01-01'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'bbb'[/color], [COLOR=red]'2007-01-01'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'ccc'[/color], [COLOR=red]'2007-01-01'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'ddd'[/color], [COLOR=red]'2007-02-01'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'eee'[/color], [COLOR=red]'2007-02-01'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'fff'[/color], [COLOR=red]'2007-02-01'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]values[/color] ([COLOR=red]'ggg'[/color], [COLOR=red]'2007-03-01'[/color], 0)

[COLOR=blue]declare[/color] @b [COLOR=blue]table[/color] (userid [COLOR=blue]char[/color](3), blah [COLOR=blue]smallint[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'aaa'[/color], 5)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'bbb'[/color], 10)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'ccc'[/color], -1)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'ddd'[/color], 69)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'eee'[/color], 1134)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'fff'[/color], 0)
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b [COLOR=blue]values[/color] ([COLOR=red]'ggg'[/color], 1)
[COLOR=blue]set[/color] [COLOR=#FF00FF]nocount[/color] [COLOR=blue]off[/color]

[COLOR=green]--this part returns the correct data
[/color][COLOR=blue]select[/color] a1.userid, a1.start, sum([COLOR=#FF00FF]coalesce[/color](c1.x, 0)) tiebreaker
[COLOR=blue]from[/color] @a a1
   [COLOR=blue]inner[/color] [COLOR=blue]join[/color] @b b1
   [COLOR=blue]on[/color] b1.userid = a1.userid
   [COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] 
      (
         [COLOR=blue]select[/color] a2.userid, a2.start, b2.blah, 1 x
         [COLOR=blue]from[/color] @a a2
            [COLOR=blue]inner[/color] [COLOR=blue]join[/color] @b b2
            [COLOR=blue]on[/color] a2.userid = b2.userid
      ) c1
   [COLOR=blue]on[/color] a1.userid <> c1.userid
      and a1.start = c1.start
      and c1.blah < b1.blah
[COLOR=blue]group[/color] [COLOR=blue]by[/color] a1.userid, a1.start
[COLOR=blue]order[/color] [COLOR=blue]by[/color] a1.start, tiebreaker


[COLOR=green]--I get the error here      
[/color][COLOR=blue]update[/color] @a
[COLOR=blue]set[/color] tiebreaker = sum([COLOR=#FF00FF]coalesce[/color](c1.x, 0))
[COLOR=blue]from[/color] @a a1
   [COLOR=blue]inner[/color] [COLOR=blue]join[/color] @b b1
   [COLOR=blue]on[/color] b1.userid = a1.userid
   [COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] 
      (
         [COLOR=blue]select[/color] a2.userid, a2.start, b2.blah, 1 x
         [COLOR=blue]from[/color] @a a2
            [COLOR=blue]inner[/color] [COLOR=blue]join[/color] @b b2
            [COLOR=blue]on[/color] a2.userid = b2.userid
      ) c1
   [COLOR=blue]on[/color] a1.userid <> c1.userid
      and a1.start = c1.start
      and c1.blah < b1.blah
[COLOR=blue]group[/color] [COLOR=blue]by[/color] a1.userid, a1.start

[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @a

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
 
Upvote 0 Downvote
Code: 
set nocount on
declare @a table (userid char(3), start smalldatetime, tiebreaker smallint)
insert into @a values ('aaa', '2007-01-01', 0)
insert into @a values ('bbb', '2007-01-01', 0)
insert into @a values ('ccc', '2007-01-01', 0)
insert into @a values ('ddd', '2007-02-01', 0)
insert into @a values ('eee', '2007-02-01', 0)
insert into @a values ('fff', '2007-02-01', 0)
insert into @a values ('ggg', '2007-03-01', 0)

declare @b table (userid char(3), blah smallint)
insert into @b values ('aaa', 5)
insert into @b values ('bbb', 10)
insert into @b values ('ccc', -1)
insert into @b values ('ddd', 69)
insert into @b values ('eee', 1134)
insert into @b values ('fff', 0)
insert into @b values ('ggg', 1)
set nocount off

select a.userid, a.start, a.tiebreaker, b.blah
from
   @a a
   inner join @b b
   on a.userid = b.userid
order by a.start, b.blah

update a
set a.tiebreaker = x.cnt
from
   @a a
   inner join (
      select a1.start, a1.userid, b1.blah, cnt = count(b2.userid)
      from
         (
            @a a1
            inner join @b b1
            on a1.userid = b1.userid
         ) left join (
            @a a2
            inner join @b b2
            on a2.userid = b2.userid
         ) on a1.start = a2.start and (b1.blah > b2.blah or (b1.blah = b2.blah and b1.userid > b2.userid))
      group by
         a1.start,
         a1.userid,
         b1.blah
   ) x on a.userid = x.userid

select * from @a
order by
   start,
   tiebreaker

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

The best part about anything that has cheese is the cheese.[/color]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
854
SashaBuilder3
SashaBuilder3
Cpreston
  • Locked
  • Question
Group results into one row 3
2
Replies
37
Views
2K
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
1K
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
Consolidate multiple rows into single row 1
Replies
4
Views
1K
Chris Miller
Chris Miller
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
709
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top