I know nothing about the REPLACE function, but the rest should hopefully
be something along these lines (though I haven't tried CASE in an update
statement before and didn't do any checking of what I have written below):

update project pj
set industrytypecode =
case
when exists(
select *
from projectrule pr
join opportunity o on pr.opportunityid = o.opportunityid
join TMICustomerProspect c on o.customerorprospectid = c.customerid
join TMIPostcodeFunding p on upper(p.postcode) = upper(c.postalcode)
where pj.projectcode = pr.projectcode
and p.funding is not null
and c.customergroupcode <> 'LARGE') then
(select p2.funding
from projectrule pr2
join opportunity o2 on pr2.opportunityid = o2.opportunityid
join TMICustomerProspect c2
on o2.customerorprospectid = c2.customerid
join TMIPostcodeFunding p2
on upper(p2.postcode) = upper(c2.postalcode)
where pj.projectcode = pr2.projectcode
and p2.funding is not null
and c2.customergroupcode <> 'LARGE')
when exists(
select *
from projectrule pr3
join opportunity o3 on pr3.opportunityid = o3.opportunityid
join TMICustomerProspect c3 on o3.customerorprospectid = c3.customerid
join TMIPostcodeFunding p3
on upper(p3.postcode) = upper(c3.postalcode)
join TMIRegion r3 on c3.postalcode starting r3.postcode
where pj.projectcode = pr3.projectcode
and r3.region = 'Northwest') then 'NWDA'
else 'COMM'
end
where pj.industrytypecode is null

HTH,
Set

Ian A. Newby wrote:

> Hi Arno,
> That one was just a suggestion, and your response was correct for
> firebird, however, I can't see anyway to do something like
>
> update project
> set industrytypecode = c.funding
> from (
> select case
> when p.funding is not null and c.customergroupcode <>
> 'LARGE' then p.funding
> when r.region = 'Northwest' then 'NWDA'
> else 'COMM'
> end as funding, pj.projectcode, pj.revisionnum
> from project pj
> join projectrule pr on pj.projectcode = pr.projectcode
> join opportunity o on pr.opportunityid = o.opportunityid
> join TMICustomerProspect c on o.customerorprospectid = c.customerid
> left join TMIPostcodeFunding p on upper(REPLACE(p.postcode,' ',''))
> = upper(REPLACE(c.postalcode,' ',''))
> left join TMIRegion r on c.postalcode like r.postcode + ' %'
> where pj.industrytypecode is null
> ) as c
> where project.projectcode = c.projectcode and project.revisionnum =
> c.revisionnum
> and coalesce(project.industrytypecode, '') <> c.funding
>
>
>
> Without using stored procedures or execute block?
>
> Regards
> Ian Newby