Home » SQL & PL/SQL » SQL & PL/SQL » updating table
updating table [message #223910] Mon, 12 March 2007 04:41 Go to next message
cutiepie
Messages: 30
Registered: July 2005
Member
Hi Experts,
i have a small problem...i wanted to update a table...the table has the following attributes with some values:
some_table:
ID, prefix_name, price, zones,period_id
=======================================
1 , ABC , 1 , 20, PEAK
2, ABC, 1 ,20 , PEAK
3, DEF, 1, 30, PEAK
4, GHI, 1, 40, OFF-PEAK
------------------------------------------
Now my question is that when the value of price (as in my case all 4) and the value of period_id (first three in my case), is same then they should update the table with the same zones...i mean it shouldnt show 20 , 20 , 30 ,40....instead it should be showing 20,20,20 and then 40 (since for the last row the value of period_id is different)
can anyone suggest me how to do that??
any help will be greatly appreciated.
thanks.
regards.
Re: updating table [message #223946 is a reply to message #223910] Mon, 12 March 2007 06:31 Go to previous messageGo to next message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
UPDATE some_table
Set zones =20
where price=1 AND period_id='PEAK';


Re: updating table [message #223947 is a reply to message #223946] Mon, 12 March 2007 06:38 Go to previous messageGo to next message
cutiepie
Messages: 30
Registered: July 2005
Member
no... Smile ..this was just an example...
i have to find all such rows that has same value in all these columns and then update them...but i cant understand how to formulate a select statement for that...there are millions of rows there.. Sad
Re: updating table [message #223953 is a reply to message #223910] Mon, 12 March 2007 07:09 Go to previous messageGo to next message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
well,

just tell me that how the zone will be deicded?

why have you chosen 20? why not 30?

will it be a user input?

Re: updating table [message #223955 is a reply to message #223953] Mon, 12 March 2007 07:13 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
So you want to update the price column of your table and set it to the minimum price of that period_id?

MHE
Re: updating table [message #223963 is a reply to message #223955] Mon, 12 March 2007 07:43 Go to previous messageGo to next message
cutiepie
Messages: 30
Registered: July 2005
Member
Thanks a lot for all the replies,
i want to update the ZONES column...(price is 1,as u can see there)...and i have to update the zones column with the same values,may be updating it with some new value...it shouldnt be 20 or 30 anymore...i can make a sequence to update the zones column...but the condition for updating the zones column is that it should be grouped according to the price and period_id...i.e. when the price for some rows is 1 and the period_id is peak then it should have some value for zones (but the same..like 20 or 40...but all the rows should be updated with this value)...
i hope now i made it clearer...
thanks again.
Re: updating table [message #223993 is a reply to message #223963] Mon, 12 March 2007 10:27 Go to previous messageGo to next message
cutiepie
Messages: 30
Registered: July 2005
Member
Problem solved...thanks everyone... Smile
Re: updating table [message #224263 is a reply to message #223910] Tue, 13 March 2007 10:40 Go to previous messageGo to next message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
Hi

then why not to share with us?

Re: updating table [message #224283 is a reply to message #224263] Tue, 13 March 2007 13:21 Go to previous message
cutiepie
Messages: 30
Registered: July 2005
Member
Hi,
i followed a simple way for that...i made a for loop cursor...as follow:
for v_cur in (select count(*) as tot,a.price,a.period_id
from some_table a, some_table b
where a.price=b.price and a.period_id=b.period_id
group by price,period_id
)
LOOP
--made another table for storing the sequenced id as follow:
--first checking if the total number of rows for each zones is
--more than 1 then:
if (v_cur.tot >1)
then
insert into temp_table (zones_id,zones_name)
values (s_some_sequence.next_val,'name');

--then getting the same value of sequence for all the rows that
--has price and period equal

select zones_id into v_zones
from temp_table
where zones_name = 'name'; /*in the insert i m generatin different names...this is just an example*/

--now the final updating
update some_table st
set st.zones = v_zones
where st.price = v_cur.price
and st.period_id = v_cur.period_id;

end if;
END LOOP;
=========================================================
THATS ALL..and i tested and it was showing me the right result.. Smile
regards.
Previous Topic: Top / Bottom Query - Oracle 00907 Error
Next Topic: Trigger Vs Alerts
Goto Forum:
  


Current Time: Sat Dec 10 06:46:25 CST 2016

Total time taken to generate the page: 0.13911 seconds