Hi Lou!

update biz bizzy
set mainsic = (select sic from bizunit b1
where b1.bizid = bizzy.bizid
and not exists(select * from bizunit b2
where b2.bizid = b1.bizid
and (b2.sales > b1.sales
or (b2.sales = b1.sales
and b2.recid > b1.recid))))
where exists(select * from bizunit b3 where b3.bizid = bizzy.bizid)

HTH and that the pizza in my oven isn't burnt,
Set

L wrote:

> Hello,
>
> I need some assistance writing an SQL statement. Below are two tables
> and I need to update the MAINSIC collumn in BIZ with the VAlue of SIC
> in the BIZUNIT table that has the MAximum sales (if there are items
> with equal sales, then it does not matter, though I might exclude a
> few SIC categories).
>
> I have done updates like this before where I have a 1 to 1
> relationship and I just want the description, but never one where I
> want the value of SIC based on it being the Maximum sales value.
>
>
> Biz
> ---------------------
> BIZID Integer (PK)
> BIZName char(60)
> MAInSIC CHAR(10)
>
>
> BizUnit
> ------------------------
> BizID Integer (PK)
> RECID Integer (PK)
> SIC char(10)
> Sales Num(15,2)
>
> Any hints would be greatly appreciated.
>
> - Lou