How to make many operations in one UPDATE statement in SQL

Question

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?

assuming there's some sort of relationship between the tables which enables you to relate a row in nr1 to a row in nr2 and nr3 then it should be simple enough to update that field in a single statement, yes. — ADyson, Jun 21 '18 at 12:43
Is that not working? Are you getting an error? Depending on how many rows you are doing at a time, that could be fine. If you are doing that for thousands of rows you maybe want to put all those fields in temp table first, then do calculation on temp table and then update table with calculated value in temp table (For performance) — Brad, Jun 21 '18 at 12:43
I think you may be looking for using a transaction, which either commits multiple updates or none of them. — Dragonthoughts, Jun 21 '18 at 12:44
Looking at your first table, with `price` and `price2` I think you may need to look at normalization, or you may land up with some very, very messy queries. — Dragonthoughts, Jun 21 '18 at 12:45
@ADyson hmm let it be to be related by inner join to nr3 table i mean let's make some id's for first two tables, but the point is how to make that update operation. @Brad the point is that if i will make `update nr3 set field= ((select price+price 2 from nr1) * select salary from nr2)/ select value from nr3;` it won't works @Dragonthoughts Nahh... im looking for the simpliest way how to make update, because when i want to make it like upper way it always make errors — Potato, Jun 21 '18 at 12:51
"the point is how to make that update operation"...well, by writing an UPDATE query? An update can have JOINed tables as its source (via a FROM clause), — ADyson, Jun 21 '18 at 12:51
You're going to need to correlate the subqueries on nr1 and nr2 to nr3. What is it that determines which rows in nr1 and nr2 match to each row in nr3? It would be helpful if you edited your question to include sample data for your three tables, along with the expected output of your update statement and the logic behind how the tables are linked. — Boneist, Jun 21 '18 at 13:00
@ADyson So if i will make some relations and i will put some columns for Foreign keys, then it's possible to make it without those `select from other table` statements? if i understand it good then it should be like `UPDATE nr3 SET field= (nr1.price+nr1.price2)*nr2.salary/value JOIN nr1 on nr1.id=nr3.id1 JOIN nr 2 on nr2.id=nr3.id2`, something like that, but im sure it wont works — Potato, Jun 21 '18 at 13:02
@Boneist i eddited some tables, just to make sure what relations we got there, i don't know if it's well organised and if it should looks like that — Potato, Jun 21 '18 at 13:09
@Potato yes it should be possible e.g. something like `UPDATE nr3 SET field = (nr1.price+nr1.price2)*nr2.salary/value FROM nr1 INNER JOIN`...etc . Possible since it's Oracle there's a slightly different syntax, but something like that. See https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle for instance — ADyson, Jun 21 '18 at 13:14

Boneist · Accepted Answer · 2018-06-21T13:12:05.717

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.

Yeee... I finally get it, thanks for help but i still don't get this Merge statement. Need to read about it... Anyway thanks very much i tested it and it works! — Potato, Jun 21 '18 at 13:25
A merge statement can be very handy if you're trying to update with subqueries - especially if the subquery also defines the set of rows being updated as well as the values to update with. I'm not so sure it's relevant in your particular case, though. I would highly recommend you read up on the Merge statement - it's main use is for upsert-style statements (i.e. insert if the row doesn't already exist, otherwise update it) but it can make some update statements faster and/or easier to read/maintain. — Boneist, Jun 21 '18 at 13:52

How to make many operations in one UPDATE statement in SQL

1 Answers1