-
advance uddate
update prenomina_rpt
set (HEXRAS) =
(select NVL(sum(ve.duracion/60),0)
from empleados_seleccion es,
v_eventos_periodo_horas ve,
eventos e
where es.id_seleccion = p_id_seleccion
and es.empleado = v_empleado
and es.id_empleado = ve.id_empleado
and ve.fecha_aplicacion >= p_fechadesde
and ve.fecha_aplicacion <= p_fechahasta
and e.id_evento = 3
and e.id_evento = ve.id_evento
group by ve.id_empleado, e.clave_evento);
can somebody help me to make this update works?.
this works fine when i have one record in empleados_seleccion table, but when this table have more than one record update HEXRAS whit the same value.
thanks in advance
-
The issue you are having here is that you are in fact setting HEXRAS to a single value that is returned in your select statement.
I don't know the full details of what you are trying to update but you may need to correlate your update statement to the select statement.
For instance this following example will give an indication of what i think you are trying to do.
Let's say you have a table you want to update, call it UTABLE. And i have put two rows into it here.
SQL> create table utable (id number, col number);
SQL> insert into utable values (1,1);
SQL> insert into utable values (2,2);
SQL> select * from utable;
ID COL
---------- ----------
1 1
2 2
And you have a table that hold the values you would like to update into UTABLE, call that table VTABLE. I have put one row in this table.
SQL> create table vtable (id number, col number);
SQL> insert into vtable values (2,5);
Now when you want to update the UTABLE with values in the VTABLE, the statement you gave as your problem looks something like this. Basically you are setting the column in all rows to a single value.
SQL> update utable set col = (select col from vtable);
2 rows updated.
SQL> select * from utable;
ID COL
---------- ----------
1 5
2 5
What you may be missing is, as in this example, that you only want to update each row based on some form of correlation between the update and select statement. In this example i really wanted to update based on the ID column in both tables. To do this i need to correlate the update to the select statement. Be caustious here as you need to include something in your WHERE clause as if there is not match on the correlation then the column will get assigned a NULL value. that is why i put in the 'WHERE EXISTS' statement that checks for a match on ID before actually updateing the row.
SQL> update utable set col = (select col from vtable where utable.id = vtable.id)
where exists (select 1 from vtable where utable.id = vtable.id);
1 row updated.
SQL> select * from utable;
ID COL
---------- ----------
1 1
2 5
hope this helps.
-
Thanks for you help, but... my problem is i'm using more than two tables and i don't know how can i correlation the other table
thanks again
-
correlation happens on key columns.
maybe if you can post the table structures and explain what you want to update, when, and where we might be able to help.