#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    3
    Rep Power
    0

    How to UPDATE FROM two tables ???


    Hello,

    I would like to make an update on one table but thecondition must be done on two tables in PL/SQL, but that doesn't work.
    I have try to methodes:

    Standard update with inner join:

    update a
    set ETAT='E', DATE_DERNIERE_MAJ = sysdate
    from fp_detail d INNER JOIN fp_active a ON
    (
    a.CLIENT = d.CLIENT AND
    a.CODE = d.CODE
    )
    WHERE
    a.CLIENT = 4 AND d.VALIDE = 1 AND
    to_date('20040921','YYYYMMDD') between a.DATE_DEBUT_VALIDITE AND a.DATE_FIN_VALIDITE AND
    a.COMPTE is not null AND
    a.ETAT = 'F'


    And an update with a cursor:

    CURSOR c_deleted IS
    SELECT a.COMPTE compte, a.etat etat
    FROM
    fp_active a, fp_detail d
    WHERE
    a.CLIENT = fp_client AND
    a.CLIENT = d.CLIENT AND
    a.CODE = d.CODE AND
    a.COMPTE is not null AND
    active_date between a.DATE_DEBUT_VALIDITE AND a.DATE_FIN_VALIDITE AND
    -- On list tous les forfait de type: F
    a.ETAT = 'F' AND
    d.VALIDE = 1
    FOR UPDATE of a.ETAT;
    data c_deleted%ROWTYPE;
    BEGIN
    LOOP
    FETCH c_deleted into data;
    EXIT WHEN c_deleted%NOTFOUND; -- pas de donnee trouvee, ou derniere ligne

    UPDATE fp_active a
    SET a.ETAT='E', a.DATE_DERNIERE_MAJ = sysdate
    WHERE CURRENT OF c_deleted;
    END LOOP;

    Thanks for your help in advance.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,382
    Rep Power
    391
    Code:
    update fp_active
       set ETAT='E', 
           DATE_DERNIERE_MAJ = sysdate
    where (client,code) in (select (client,code) from fp_detail
          where valide = 1)
    and CLIENT = 4 
    AND to_date('20040921','YYYYMMDD') between DATE_DEBUT_VALIDITE AND DATE_FIN_VALIDITE 
    AND COMPTE is not null 
    AND ETAT = 'F'
    What doi you mean by standard update?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    3
    Rep Power
    0

    Thumbs up Thanks


    Thats works but you must remove the paranthesis arround de column result inside de select in.

    ...in (select client,code from fp_detail ...

    Originally Posted by swampBoogie
    Code:
    update fp_active
       set ETAT='E', 
           DATE_DERNIERE_MAJ = sysdate
    where (client,code) in (select (client,code) from fp_detail
          where valide = 1)
    and CLIENT = 4 
    AND to_date('20040921','YYYYMMDD') between DATE_DEBUT_VALIDITE AND DATE_FIN_VALIDITE 
    AND COMPTE is not null 
    AND ETAT = 'F'
    What doi you mean by standard update?
    I mean without cursor use
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    3
    Rep Power
    0

    Question In case of a lot of data


    I would like to known if there is another methode. Because the select in can return a lot of data and in that case the update can take a lot of time.

IMN logo majestic logo threadwatch logo seochat tools logo