Home » SQL & PL/SQL » SQL & PL/SQL » update statement behavior
update statement behavior [message #234154] Mon, 30 April 2007 04:10 Go to next message
beetel
Messages: 96
Registered: April 2007
Member
I have an UPDATE statement with an inner query:

update test1 a
set (case, gross) = (select (a.case + 1) * d.mult,
(a.gross + 1) * d.mult
from test2 d
where d.dim_id = a.dim_id);

test2 table is a master table with dim_id as Primary Key. test1 is a table which references test2.dim_id. The relationship between test1 and test2 is ONE-to-MANY.

Let's say I have:

create table test1
(id number,
case number,
gross number,
dim_id number) tablespace ufpc_ts;
create table test2
(dim_id number,
mult number) tablespace ufpc_ts;
insert into test2 values (1, 2.2);
insert into test1 values (1, 0, 0, 1);
insert into test1 values (2, 0, 0, 1);
insert into test1 values (3, 0, 0, 1);
insert into test1 values (4, 0, 0, 1);
insert into test1 values (5, 0, 0, 1);
insert into test1 values (6, 0, 0, 1);
commit;

In my UPDATE stmt:

update test1 a
set (case, gross) = (select (a.case + 1) * d.mult,
(a.gross + 1) * d.mult
from test2 d
where d.dim_id = a.dim_id);

will my inner query return 6 rows because it joins with test1 table on dim_id???

Re: update statement behavior [message #234181 is a reply to message #234154] Mon, 30 April 2007 05:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ It will return nothing as this is not a valid Oracle SQL statement.
2/ One valid statement may be (assuming you add primary key on your tables):
update 
  (select a.case, a.gross, d.mult
   from test 1 a, test2 d
   where d.dim_id = a.dim_id)
set case = (case + 1) * mult,
    gross = (gross + 1) * mult
/

And this will update only one row as there is only one row in test2 matching a row in test1.

Regards
Michel

Re: update statement behavior [message #234243 is a reply to message #234181] Mon, 30 April 2007 10:16 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Eeeh, I would say it returns 6 rows, or am I missing something?
(ok, the result is that case and gross still remains 0 because 0*2.2 is 0, but I do get 6 rows with all 0's Razz)


SQL> SELECT a.id
  2        ,a.CASE
  3        ,a.CASE * b.mult new_case
  4        ,a.gross
  5        ,a.gross * b.mult new_gross
  6  FROM   test1 a
  7        ,test2 b
  8  WHERE  a.dim_id = b.dim_id
  9  /

        ID       CASE   NEW_CASE      GROSS  NEW_GROSS
---------- ---------- ---------- ---------- ----------
         1          0          0          0          0
         2          0          0          0          0
         3          0          0          0          0
         4          0          0          0          0
         5          0          0          0          0
         6          0          0          0          0

6 rows selected
Re: update statement behavior [message #234252 is a reply to message #234243] Mon, 30 April 2007 10:38 Go to previous message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oops, sorry I join with the first id column and not the last one (dim_id). Embarassed

(In addition, CASE is a reserved word in Oracle, avoid using it.)

Regards
Michel
Previous Topic: Export schema with DBMS_metadata
Next Topic: Warning: Procedure created with compilation errors
Goto Forum:
  


Current Time: Mon Dec 05 02:49:17 CST 2016

Total time taken to generate the page: 0.06836 seconds