CODEWITHSUNDEEP
sqloracle-database
Potato
Potato

Reputation: 172

How to make many operations in one UPDATE statement in SQL

what is the best way to make multiple operations in one statement in update i want for example to update field column of nr3 table.

Create table nr1
(
id int,
price int,
price2 int,
PRIMARY KEY (id)
);

create table nr2 
(id int,
salary int
PRIMARY KEY (id)
);

create table nr3
(id int,
id1 int,
id2 int,
value int,
field int,
PRIMARY KEY (id),
FOREIGN KEY (id1) REFERENCES nr1(id),
FOREIGN KEY (id2) REFERENCES nr2(id)
);

and for example i want to make operation 

nr3.field= ((nr1.price+nr1.price2)* nr2.salary)/nr3.value;

How to make it in the best way, i need to have some template, because i think i don't get it, it's possible to make it in one UPDATE or i need to make some other "temporary" columns?

Upvotes: 0

Views: 80

Answers (1)

Boneist
Boneist

Reputation: 23588

Assuming the id column is what links nr2 and nr1 to nr3 (and that the id column is unique in each table), perhaps this is what you're after:

update nr3
set    nr3.field = ((select nr1.price + nr1.price2 from nr1 where nr1.id1 = nr3.id)
                    * (select nr2.salary from nr2 where nr2.id = nr3.id2))
                   / nr3.value;

Alternatively, a MERGE statement may be of more use, assuming that nr1.id and nr2.id are the same:

merge into nr3 tgt
  using (select nr1.id,
                (nr1.price + nr1.price2) * nr2.salary new_val
         from   nr1
                inner join nr2 on nr1.id = nr2.id) src
  on (tgt.id = src.id)
when matched then
  update set tgt.field = src.new_val / tgt.value;

However, from your edits to the nr3 table, it may not be safe to assume nr1.id = nr2.id. If that's the case, stick with the update statement above, otherwise you'll need to include nr3 in the src subquery, which means an extra join on the table. That could well be less efficient than the corresponding update statement.

Upvotes: 1

Related Questions