# Update of a table comparing or calculating with another [closed]

2

I need to do an Update of one table comparing or calculating with another.

I have a table of `jugadores` with all your data including `sueldo` , `rut` , and other fields. Also, I have the table `partidos` with its respective `id` for each match and `rut` of each player. Then, each player who has played more than 50 games (can be 10, 20 or any other number) is assigned double the salary in the player table (it can be a percentage more), but it depends on the number of games played .

So, in what I tried so far, I count the `id` of matches when the `rut` is the same (or can be another field).

Generalizing what I tried:

``````Update jugadores
Set sueldo = sueldo*2
Join partidos
on(jugdores.rut=partidos.rut)
Having count(partidos.id)>50;
``````

... but I get errors `ora-00933` .

asked by Eduardo Oyarzun 27.11.2017 в 06:15
source

3

According to what I have understood, what you need is the following:

``````UPDATE T1 SET T1.C1 = T1.C1*3 WHERE T1.C2 = (SELECT T2.C2 FROM T2 WHERE T1.C2 = T2.C2)
``````

Of course, the condition of having would be missing, but with what you have written, I do not understand the objective nor what the grouping condition would be.

``````UPDATE JUGADORES J
SET J.SALARIO = J.SALARIO*2
WHERE J.ID IN (SELECT TOT_P.ID
FROM (SELECT P.ID, COUNT(*)
FROM PARTIDOS P
GROUP BY P.ID
HAVING COUNT(*) > 50) TOT_P);
``````

-1

If it is only about multiplying the salary by `2` when a player has played more than `50` matches, then you can simply avoid doing the join by moving the% share% off the% main% co within a subquery .

I leave you 2 options, one of which is similar to the one presented by @Miguel in its good response, but simplified:

Using `partidos` :

``````update jugadores
set sueldo = sueldo * 2
where rut in (select rut
from partidos
group by rut
having count(*) > 50)
``````

Or using `update` :

``````update jugadores j
set j.sueldo = j.sueldo * 2
where exists (select null
from partidos p
where p.rut = j.rut
having count(*) > 50);
``````

Now, if the idea is that you will want to put the salary up to date depending on the number of games played, such as avalanches with the following comments:

Then, each player who has played more than 50 matches ( can be 10, 20 or any other number ) is given double the salary in the table player ( may be a percentage more ), but depends on the number of matches played .

... then it is necessary to restructure the query so that the `IN` can take into account the number of matches when allocating the new salary.

To give an example, let's say that the desired logic is that, if the player has played more than 20 games, then he receives a 50% increase, but if he has played more than 50 games, then the salary doubles.

Normally, this type of `EXISTS` would be done with a join, but with Oracle, doing it this way, although not impossible, is extremely uncomfortable. The best thing in this case is to use the statement `UPDATE` :

``````merge into jugadores j
using (
select rut,
from partidos
group by rut
having count(*) > 20
) p
on (p.rut = j.rut)
when matched then
update set j.sueldo = case when p.cantidad > 50
then j.sueldo * 2
else j.sueldo * 1.5
end;
``````

If you want to use a `UPDATE` , the query would be written like this:

``````update (
select j.rut,
j.sueldo,
join (select rut, count(*) as cantidad
from partidos
group by rut
having count(*) > 20) p
on p.rut = j.rut
)
set sueldo = case when cantidad > 50
then sueldo * 2
else sueldo * 1.5
end;
``````

But, again, I recommend you use the `MERGE` statement since to write a `UPDATE` statement that includes joins and that runs successfully, you must be very careful not to receive the following error:

ORA-01732: data manipulation operation not legal on this view

In fact, I think that the rules as to what types of joins in the updates are allowed have been changing from version to version. So it's possible that the example of `MERGE` that I put to you does not work according to your version of Oracle.