set a field = min(value) of another field

[bmacbmac]

Hi,

I have two tables:
create table mytable1 (docno int, legal varchar(100), dtfirst int)
create table mytable2 (docno1 int, legal varchar(100))


With the following data:
insert into mytable1 VALUES (12345, 'L1B1ANDERSON', null)
insert into mytable1 VALUES (22222, 'L2B2SMITH', null)
insert into mytable1 VALUES (33333, 'L5B5JONES', null)

insert into mytable2 VALUES (44444, 'L1B1ANDERSON')
insert into mytable2 VALUES (55555, 'L1B1ANDERSON')
insert into mytable2 VALUES (66666, 'L1B1ANDERSON')
insert into mytable2 VALUES (77777, 'L2B2SMITH')
insert into mytable2 VALUES (88888, 'L2B2SMITH')
insert into mytable2 VALUES (88888, 'L2B2SMITH')


I would like to set mytable1.dtfirst = min(mytable2.docno) grouped by mytabl2.legal


something kind of like this:

update mytable1
set dtfirst = min(docno1)
from mytable1
join mytable2 on mytable1.legal = mytable2.legal
group by mytable2.legal

But of course I get this error: An aggregate may not appear in the set list of an UPDATE statement.

Is there another way I can do this? This is SQL 2000, but I can get it into 2008 if I absolutely need to use a specific 2008 function.

Thanks in advance!

 

[TamarGranor]

Try this:

UPDATE mytable1
	SET dtfirst = MinCurs.MinDocNo 
	FROM (SELECT MIN(mytable2.docno1) MinDocNo , mytable2.legal FROM mytable2 GROUP BY legal) MinCurs
	WHERE mytable1.legal = MinCurs.legal

Tamar

 

[bmacbmac]

that worked perfectly! Thanks!