Home » SQL & PL/SQL » SQL & PL/SQL » update (10g)
update [message #362991] Thu, 04 December 2008 14:09 Go to next message
nandac
Messages: 41
Registered: July 2006
Location: jersey city, usa
Member

will the following update work in oracle?

update tablespace_rep set maxspaceused = (select max(DAILY_GROWTH) from TS_DAILY_GROWTH where TABLESPACE_NAME = ts_name group by TABLESPACE_NAME);

idea :

i have two tables tablespace_rep and TS_DAILY_GROWTH. the first table contains information about tablespace total space, space used, free etc. the second table contains information about daily space growth of each table space - for a week. so each tablespace will have atleast 7 records in TS_DAILY_GROWTH.

now i want to take the maximum growth value for each tablespace in TS_DAILY_GROWTH and update the corresponding record in tablespace_rep.
Re: update [message #362992 is a reply to message #362991] Thu, 04 December 2008 14:23 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@nandac,

The update won't work if your subquery

select max(DAILY_GROWTH) from TS_DAILY_GROWTH 
where TABLESPACE_NAME = ts_name group by TABLESPACE_NAME


returns more than one row. To avoid such error make your subquery a correlated one. i.e. make it dependent on the table which you are trying to update (here it will be tablespace_rep)

This is one of the errors I can think of. Maybe others may point out more.

Why don't you try running the statement and see if you get any error?

Regards,
Jo
Re: update [message #362993 is a reply to message #362991] Thu, 04 December 2008 14:30 Go to previous messageGo to next message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
With the use of the MAX() function and the GROUP BY there shouldn't be any
chance of getting more than one row, correct?

Thanks,
Jim

[Updated on: Thu, 04 December 2008 14:31]

Report message to a moderator

Re: update [message #362995 is a reply to message #362992] Thu, 04 December 2008 14:33 Go to previous messageGo to next message
nandac
Messages: 41
Registered: July 2006
Location: jersey city, usa
Member

the problem is that i dont have access to sqlplus. i just have a program which connects to oracle and i'm trying to execute these statements in that. but the update is a correlated one - the select references a column in the updated table.
Re: update [message #362997 is a reply to message #362993] Thu, 04 December 2008 14:46 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@knicely87,

Using GROUP BY Clause splits the records into groups. Hope the following example helps you understand what I am pointing at:
SQL> drop table test_tab;

Table dropped.

SQL> CREATE TABLE TEST_TAB
  2  (
  3  col_1 NUMBER,
  4  col_2 VARCHAR2(10)
  5  );

Table created.

SQL> INSERT INTO test_tab values(100, 'A');

1 row created.

SQL> INSERT INTO test_tab VALUES(200, 'B');

1 row created.

SQL>  INSERT INTO test_tab values(300, 'A');

1 row created.

SQL> SELECT * FROM test_tab;

     COL_1 COL_2
---------- ----------
       100 A
       200 B
       300 A

3 rows selected.

SQL> SELECT MAX(col_1) FROM test_tab
  2  GROUP BY col_2;

MAX(COL_1)
----------
       300
       200

2 rows selected.

SQL> CREATE TABLE test_tab_new
  2  (
  3  col_2 number
  4  );

Table created.

SQL> INSERT INTO test_tab_new
  2  VALUES(10000);

1 row created.
SQL>  UPDATE test_tab_new
  2  SET col_2 = (SELECT MAX(col_1) FROM test_tab
  3  GROUP BY col_2);
SET col_2 = (SELECT MAX(col_1) FROM test_tab
             *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row


SQL> 


Hope this helps,

Regards,
Jo

[Updated on: Thu, 04 December 2008 14:47]

Report message to a moderator

Re: update [message #363001 is a reply to message #362997] Thu, 04 December 2008 14:55 Go to previous messageGo to next message
nandac
Messages: 41
Registered: July 2006
Location: jersey city, usa
Member

but you have not co-related the updated table with the selected table. the update statement i have has a reference to the updated table in the select with group by.
Re: update [message #363003 is a reply to message #362991] Thu, 04 December 2008 14:58 Go to previous messageGo to next message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
nandac is using a correlated subquery using "where TABLESPACE_NAME = ts_name".

I think he wants something like this:

SQL> create table tablespace_rep (ts_name varchar2(100), maxspaceused NUMBER(10));

Table created.

SQL> create table ts_daily_growth (tablespace_name varchar2(30), day_num number(1), daily_growth num
ber(10));

Table created.

SQL> 
SQL> insert into tablespace_rep values ('ts1', null);

1 row created.

SQL> insert into tablespace_rep values ('ts2', null);

1 row created.

SQL> 
SQL> insert into ts_daily_growth values ('ts1', 1, 100);

1 row created.

SQL> insert into ts_daily_growth values ('ts1', 2, 200);

1 row created.

SQL> insert into ts_daily_growth values ('ts1', 3, 50);

1 row created.

SQL> insert into ts_daily_growth values ('ts1', 4, 400);

1 row created.

SQL> insert into ts_daily_growth values ('ts1', 5, 400);

1 row created.

SQL> insert into ts_daily_growth values ('ts1', 6, 230);

1 row created.

SQL> insert into ts_daily_growth values ('ts1', 7, 20);

1 row created.

SQL> insert into ts_daily_growth values ('ts2', 1, 50);

1 row created.

SQL> insert into ts_daily_growth values ('ts2', 2, 900);

1 row created.

SQL> insert into ts_daily_growth values ('ts2', 3, 650);

1 row created.

SQL> insert into ts_daily_growth values ('ts2', 4, 200);

1 row created.

SQL> insert into ts_daily_growth values ('ts2', 5, 300);

1 row created.

SQL> insert into ts_daily_growth values ('ts2', 6, 100);

1 row created.

SQL> insert into ts_daily_growth values ('ts2', 7, 600);

1 row created.

SQL> 
SQL> select * from tablespace_rep
  2  
SQL> update tablespace_rep set maxspaceused = (select max(DAILY_GROWTH) from TS_DAILY_GROWTH where T
ABLESPACE_NAME = ts_name group by TABLESPACE_NAME);

2 rows updated.

SQL> select * from tablespace_rep;

TS_NAME        MAXSPACEUSED       
-------------- ------------
ts1            400
ts2            900



Thanks,
Jim

[Updated on: Thu, 04 December 2008 15:16]

Report message to a moderator

Re: update [message #363006 is a reply to message #363001] Thu, 04 December 2008 15:16 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@knicely87,

Not always true.
See the below examples: (Don't go by the columns I have used)
SQL> SELECT * FROM test_tab;

     COL_1 COL_2
---------- ----------
       100 A
       200 B
       300 A
       300 B

4 rows selected.

SQL> SELECT * FROM test_tab_new;

     COL_A      COL_B
---------- ----------
       100      99999
       300      99999

2 rows selected.

SQL> 
SQL>  UPDATE test_tab_new a1
  2   SET a1.col_b = (Select MAX(col_1) FROM test_tab b1
  3   WHERE a1.col_A = b1.col_1
  4   GROUP BY b1.col_2);
 SET a1.col_b = (Select MAX(col_1) FROM test_tab b1
                 *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row



It also depends on what is the kind of relationship the corelated relationship is having. If it is one(main query) to many (sub query) then the GROUP BY Clause will return multiple records. (Oops!!! I forgot to mention this in my first post...)

Hope this helps,

Regards,
Jo

[Updated on: Thu, 04 December 2008 15:24]

Report message to a moderator

Re: update [message #363012 is a reply to message #362991] Thu, 04 December 2008 15:32 Go to previous messageGo to next message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
I understand your example, but to be more like the original
poster's example shouldn't the test_tab_new's col_a have
the values 'A' and 'B'?

SQL> select * from test_tab;

     COL_1 COL_2
---------- ----------
       100 A
       200 B
       300 A
       300 B

SQL> select * from test_tab_new;

COL_A           COL_B
---------- ----------
A               99999
B               99999

SQL> 
SQL> UPDATE test_tab_new a1
  2     SET a1.col_b = (Select MAX(col_1) FROM test_tab b1
  3     WHERE a1.col_A = b1.col_2
  4     GROUP BY b1.col_2);

2 rows updated.

SQL>  select * from test_tab_new;

COL_A           COL_B
---------- ----------
A                 300
B                 300



Anyway, hopefully nandac has been helped. I was, thanks, Jo.

-Jim

[Updated on: Thu, 04 December 2008 15:47] by Moderator

Report message to a moderator

Re: update [message #363017 is a reply to message #363006] Thu, 04 December 2008 15:48 Go to previous message
nandac
Messages: 41
Registered: July 2006
Location: jersey city, usa
Member

guys i got access to sqlplus and with some experimentation this is working now, thanks for all your help!
Previous Topic: date format to dd-mm-yyyy
Next Topic: Seperate Parts of English Sentence
Goto Forum:
  


Current Time: Mon Dec 05 09:16:12 CST 2016

Total time taken to generate the page: 0.09526 seconds