Home » SQL & PL/SQL » SQL & PL/SQL » Updation (Oracle 10g)
Updation [message #412924] |
Mon, 13 July 2009 07:41  |
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   |
joy_division
Messages: 4963 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 #412930 is a reply to message #412927] |
Mon, 13 July 2009 07:57   |
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   |
pablolee
Messages: 2882 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   |
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   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
1. Quote: | but it was not updating
|
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   |
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 #412949 is a reply to message #412945] |
Mon, 13 July 2009 09:46   |
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  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|
Goto Forum:
Current Time: Thu Feb 13 10:54:39 CST 2025
|