Home » SQL & PL/SQL » SQL & PL/SQL » Updation (Oracle 10g)
Updation [message #412924] Mon, 13 July 2009 07:41 Go to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear All,

I have a table which holds the sales information which is shown below. I am need to update the values internally in such a way that Q1 cost should be updated in the Q3 cost and Q2 cost should be updated in the Q4 cost.Could anyone help me with this how to do the internal updation.


PRODUCTS   REGION     PE       COST
---------- ---------- -- ----------
JEANS      CHENNAI    Q1        374
SHIRTS     CHENNAI    Q1        375
JEANS      MUMBAI     Q3          0
SHIRTS     MUMBAI     Q3          0



Thanks and Regards,
Hammer
Re: Updation [message #412926 is a reply to message #412924] Mon, 13 July 2009 07:47 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
aviva4500 wrote on Mon, 13 July 2009 08:41
Q1 cost should be updated in the Q3 cost and Q2 cost should be updated in the Q4 cost


Based on what condition? Products, region, both, niether?
Re: Updation [message #412927 is a reply to message #412924] Mon, 13 July 2009 07:49 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Have you tried anything?
Re: Updation [message #412930 is a reply to message #412927] Mon, 13 July 2009 07:57 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear pablolee,
Thankyou for your immediate reply, Based on product and region.
Yes, I am trying with lag and lead , i think it is a wrong guess.
Can you help me out with this.



Thanks and Regards,
Hammer
Re: Updation [message #412931 is a reply to message #412930] Mon, 13 July 2009 08:00 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Show us what you have tried, show us your expected results, even better, do all that and post a working test case. Based on your rather vague description and data, I would say that no rows should be updated at all.
Re: Updation [message #412937 is a reply to message #412931] Mon, 13 July 2009 08:15 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear pablolee,

I have tried with the below query but it was not updating, sorry if i am wrong.

  1  update test1 t
  2  set t.cost =(select a.cost from test1 a
  3  where a.products = t.products and
  4* a.region=t.region)
SQL> /

4 rows updated.



SQL> select * from test1;

PRODUCTS   REGION     PE       COST
---------- ---------- -- ----------
JEANS      CHENNAI    Q1        374
SHIRTS     CHENNAI    Q1        375
JEANS      MUMBAI     Q3          0
SHIRTS     MUMBAI     Q3          0






Test case:
---------
create table test
(products varchar2(10),
region varchar2(10),
period varchar(2),
cost integer)

INSERT INTO TEST1 VALUES ('SHIRTS','MUMBAI','Q3',0);
INSERT INTO TEST1 VALUES ('JEANS','MUMBAI','Q3',0);
INSERT INTO TEST1 VALUES ('SHIRTS','CHENNAI','Q1',375);
INSERT INTO TEST1 VALUES ('JEANS','CHENNAI','Q1',374);



Thanks and Regards,
Hammer


[Updated on: Mon, 13 July 2009 08:24]

Report message to a moderator

Re: Updation [message #412940 is a reply to message #412937] Mon, 13 July 2009 08:39 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
1.
Quote:
but it was not updating

Quote:
4 rows updated.


Clearly it was updating. It updated 4 rows.

Quote:
Yes, I am trying with lag and lead ,
Not according to your posted example your not.
Thanks for the test case, but you still have not posted the expected results.
Re: Updation [message #412943 is a reply to message #412940] Mon, 13 July 2009 09:19 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear All,

Here in the actual data for chennai Q3 value is zero, the result expected is the value of Q1 should be the value of Q3.
Actual table data:
----------------------
PRODUCTS   REGION     PE       COST
---------- ---------- -- ----------
JEANS      CHENNAI    Q1        374
SHIRTS     CHENNAI    Q1        375
JEANS      CHENNAI    Q3          0
SHIRTS     CHENNAI    Q3          0
JEANS      DELHI      Q2        274
SHIRTS     DELHI      Q2        275
JEANS      DELHI      Q4          0
SHIRTS     DELHI      Q4          0



Result:
--------

PRODUCTS   REGION     PE       COST
---------- ---------- -- ----------
JEANS      CHENNAI    Q1        374
SHIRTS     CHENNAI    Q1        375
JEANS      CHENNAI    Q3        374
SHIRTS     CHENNAI    Q3        375
JEANS      DELHI      Q2        274
SHIRTS     DELHI      Q2        275
JEANS      DELHI      Q4        274
SHIRTS     DELHI      Q4        275



Kindly help me out with this.


Thanks and Regards,
Hammer.
Re: Updation [message #412945 is a reply to message #412943] Mon, 13 July 2009 09:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the test case for this.

Regards
Michel
Re: Updation [message #412949 is a reply to message #412945] Mon, 13 July 2009 09:46 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear Michel,
Thankyou for your immediate reply. Here below i have posted the test case for your kind reference.

Test case:
----------
CREATE TABLE TEST1
(PRODUCTS VARCHAR2(10),
REGION VARCHAR2(10),
PERIOD VARCHAR2(4),
COST INTEGER)
/


INSERT INTO TEST1 VALUES('JEANS','CHENNAI','Q1',374);
INSERT INTO TEST1 VALUES('JEANS','CHENNAI','Q1',375);
INSERT INTO TEST1 VALUES('JEANS','DELHI','Q2',274);
INSERT INTO TEST1 VALUES('JEANS','DELHI','Q2',275);
INSERT INTO TEST1 VALUES('JEANS','CHENNAI','Q3',0);
INSERT INTO TEST1 VALUES('JEANS','CHENNAI','Q3',0);
INSERT INTO TEST1 VALUES('JEANS','DELHI','Q4',0);
INSERT INTO TEST1 VALUES('JEANS','DELHI','Q4',0);



Thanks and Regards,
Hammer
Re: Updation [message #412961 is a reply to message #412949] Mon, 13 July 2009 11:38 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In your update statement you missed 2 points:
1/ you update only Q3 and Q4
2/ you update them with the value of Q1 and Q2.

Regards
Michel
Previous Topic: sql query tunning
Next Topic: Help creating view for each record...
Goto Forum:
  


Current Time: Sat Dec 03 10:08:17 CST 2016

Total time taken to generate the page: 0.09752 seconds