Results 1 to 4 of 4

Thread: advance uddate

  1. #1
    Join Date
    May 2006
    Posts
    2

    Lightbulb 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

  2. #2
    Join Date
    May 2006
    Posts
    9
    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.

  3. #3
    Join Date
    May 2006
    Posts
    2
    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

  4. #4
    Join Date
    May 2006
    Posts
    9
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •