Subject Re: [firebird-support] SQL Update - Need Help
Author Svein Erling Tysvaer
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,

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)
> BizUnit
> ------------------------
> BizID Integer (PK)
> RECID Integer (PK)
> SIC char(10)
> Sales Num(15,2)
> Any hints would be greatly appreciated.
> - Lou