Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    62

    Question Unanswered: Join in update query

    Hi,

    The following query works fine in SQL Server and I would like to achieve the same in Oracle 9i. It does not work in Oracle. Can any one help who knows the equivalent of this in Oracle ?

    UPDATE WEB_FSP_DATA a, WEB_DA_CERTIFICATION b
    SET a.address_qualified_date = SYSDATE,
    a.da_completed_id = b.da_certified
    WHERE a.wire_cntr_cd = b.wire_cntr_cd
    AND a.da_cd = b.da_cd
    AND b.da_certified = 'Y'
    AND a.da_completed_ind IS NULL;

    Thanks.
    -Bheem

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Such a query might work:
    Code:
    UPDATE web_esp_data a
       SET (a.address_qualified_date, a.da_completed_id) =
              (SELECT SYSDATE, b.da_certified
                 FROM web_da_certification b
                WHERE a.wire_cntr_cd = b.wire_cntr_cd
                  AND a.da_cd = b.da_cd
                  AND b.da_certified = 'Y')
     WHERE a.da_completed_ind IS NULL;
    I don't know SQL used in SQL Server (therefore, I don't know is "a.da_completed_ind is null" part of a subquery or condition which defines records to be updated) so the last condition might also be as follows:
    Code:
    UPDATE web_esp_data a
       SET (a.address_qualified_date, a.da_completed_id) =
              (SELECT SYSDATE, b.da_certified
                 FROM web_da_certification b
                WHERE a.wire_cntr_cd = b.wire_cntr_cd
                  AND a.da_cd = b.da_cd
                  AND b.da_certified = 'Y'
                  AND a.da_completed_ind IS NULL);

Posting Permissions

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