On May 7, 9:40 am, Dom <dolivas...@gmail.com> wrote:
> That is updating only one record. And that one record is hard-coded
> into the SQL, eg, "Where Book_Key = 'B102'. I need to update every
> record, and I can't run a separate script for each record.
>
> On May 6, 4:46 pm, Javier Montani <jmont...@gmail.com> wrote:
>
>
>
> > This may help:http://www.oracle-training.cc/t_garmany_easyoracle_UPDATE.htm
>
> > From there:
> > SQL> update
> > 2 sales
> > 3 set (order_date, quantity) = (select
> > 4 SYSDATE,
> > 5 avg(quantity)
> > 6 from sales
> > 7 where book_key = 'B102'
> > 8 group by book_key, SYSDATE)
> > 9 where book_key = 'B102';
>
> > 2009/5/6 Dom <dolivas...@gmail.com>
>
> > > I usually work on SQL-Server. But I'm doing pretty important work now
> > > on an Oracle database, and althought the transistion was pretty
> > > easy ... except for the UPDATE statement.
>
> > > I was wondering if, instead of explaining what I want, I just stated
> > > the UPDATE as it would be done in SQL-SERVER, and let someone
> > > translate it for me, along with a brief explanation about the general
> > > approach behind it. It's a fairly complicated one, with subqueries
> > > and aggregate functions.
>
> > > Here is table T_Inv_Region (Patent_ID / Region_NM is unique) ------
> > > Patent_ID
> > > Region_NM
> > > Region_Count
>
> > > And here is table T_Inv (Patent_ID / Region_NM is not unique) -------
> > > Patent_ID
> > > Region_NM
>
> > > And here is the update statement as it would be done in T-SQL: ---
> > > Update T_Inv_Region
> > > Set Region_CT = x.Region_CT
> > > From T_Inv_Region r
> > > left join (
> > > Select Patent_ID, Region_NM,
> > > Count (*) as Region_CT
> > > From T_Inv
> > > Group By Patent_ID, Region_NM
> > > ) x on r.Patent_ID = x.Patent_ID and
> > > r.Region_NM = x.Region_NM- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
Try this:
Update T_Inv_Region
Set Region_CT = (select x.Region_CT
>From T_Inv_Region r
left join (
Select Patent_ID, Region_NM,
Count (*) as Region_CT
From T_Inv
Group By Patent_ID, Region_NM
) x on r.Patent_ID = x.Patent_ID and
r.Region_NM = x.Region_NM)
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
