Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Posts
    8

    Red face Unanswered: Need help with an Oracle Update SQL using a multiple table join

    I need help with creating an Oracle Update SQL structure that joins 3 tables and has a subquery. I have created the SQL select that returns all the rows I'm looking for, now I need to incorporate it into an update statement.

    Here is the SQL that I used to select the rows to make sure that I am returning the correct rows. This SQL will have to be used in the UPDATE.

    select a1.location, a1.desc, a1.meter
    from notice a1
    ,account b1
    ,utility c1
    where a1.co = b1.co
    and b1.co = c1.co
    and a1.acct = b1.acct
    and b1.location = c1.location
    and a1.desc = 'MULTIPLE'
    and 2 > (select count(*)
    from account b2
    where b2.co = a1.co
    and b2.acct = a1.acct
    and b2.status = 'A')

    The notice table is the main table, and it is the table that needs to be updated. The account and utility tables are used to get the data to load in the columns that are updated in the notice table. The three columns that need to be updated on the notice table are location, desc, and meter.

    The location column on the notice table should be loaded with data from the location column on the account (b1) table. The desc column on the notice table should be loaded with data from the desc column on the utility (c1) table. The meter column on the notice table should be loaded with NULL.

    The subselect on the second account table is needed to insure that there is only one active account row.

    Can anyone help me with creating an update statement that uses this select???

  2. #2
    Join Date
    Jul 2006
    Posts
    8

    I forgot to mention I'm using Oracle 8i. (eom)

    I forgot to mention I'm using Oracle 8i. (eom)

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    DESC is reserved word and can not be used as column name; I renamed it to "DESC_COL". Query might (or might not) be like this:
    Code:
    UPDATE notice a1
       SET (a1.LOCATION, a1.desc_col, a1.meter) =
              (SELECT b1.LOCATION, c1.desc_col, NULL
                 FROM ACCOUNT b1, utility c1
                WHERE a1.co = b1.co
                  AND b1.co = c1.co
                  AND a1.acct = b1.acct
                  AND b1.LOCATION = c1.LOCATION
                  AND 2 >
                         (SELECT COUNT (*)
                            FROM ACCOUNT b2
                           WHERE b2.co = a1.co
                             AND b2.acct = a1.acct
                             AND b2.status = 'A'))
     WHERE a1.desc_col = 'MULTIPLE'

Posting Permissions

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