Results 1 to 13 of 13
  1. #1
    Join Date
    May 2004
    Posts
    184

    Unanswered: UPDATE with Multiple Tables

    I thought I had a previous script I wrote that updated multiple columns where I also had mulitple tables in my join condition, but I cannot seem to find it, and the only examples I can seem to find online use subselects which I don't think I need to do this, but I maybe wrong.

    I've written the syntax below like I would have written this in MS SQL, but I know Oracle has a different syntax for this.

    Any help would be appreciated.

    Thank you.

    Robert

    UPDATE uLat
    SET (ULAT.enrollmentStatus='I', seatAllocationID=null
    FROM drLearningActivityTranscript uLat inner join drLearningActivityInstance LAI on ulat.learningActivityInstanceID=ulat.learningActiv ityInstanceID
    where ulat.id IN (
    select lat.id from drLearningActivityTranscript lat
    inner join drLearningActivityInstance Lai on lai.id=lat.learningActivityInstanceID
    where enrollmentstatus in ('E','W') and firstMEetingSTart > sysdate)
    and ulat.enrollmentStatus ='W' and firstMeetingSTart < sysdate

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Quote Originally Posted by rtnetworks
    ...and the only examples I can seem to find online use subselects which I don't think I need to do this, but I maybe wrong...
    In Oracle the only way how to update your table is to use subselects. So use example you've find.

  3. #3
    Join Date
    Nov 2005
    Posts
    17
    Your MS SQL example is very close. Just a slight modification to the SET clause at line 2 and parenthesis needed around the subquery.

    UPDATE uLat
    SET (ULAT.enrollmentStatus, seatAllocationID) =
    (SELECT 'I', null
    FROM drLearningActivityTranscript uLat
    inner join drLearningActivityInstance LAI
    on ulat.learningActivityInstanceID=ulat.learningActiv ityInstanceID
    where ulat.id IN (select lat.id
    from drLearningActivityTranscript lat
    inner join drLearningActivityInstance Lai
    on lai.id=lat.learningActivityInstanceID
    where enrollmentstatus in ('E','W')
    and firstMEetingSTart > sysdate))
    and ulat.enrollmentStatus ='W'
    and firstMeetingSTart < sysdate;

  4. #4
    Join Date
    May 2004
    Posts
    184
    cmerry,

    You're awesome... I remembered there was something weird about Oracle needing the parethesis and I tried a few variations, but missed where the parenthesis really needed to go. Thank you.

    So, that I understand this for the future. If I need to update a column and my underlying query has only one table, I don't need a subselect (unless of course the value comes from a subselect). On the other hand, if my underlying query contains more than one table, I must use parenthesis using the syntax of:

    Code:
    UPDATE table
    SET (fieldlist)=(underlying table and criteria)
    Is this correct?

    Thanks,

    Robert

  5. #5
    Join Date
    May 2004
    Posts
    184
    cmerry,

    I tried your query, but it tells me that the SQL statement is not properly ended and highlights just before "and ulat.enrollmentStatus ='W' "

    I tried the following...
    UPDATE uLat
    SET (ULAT.enrollmentStatus, seatAllocationID) =
    (SELECT 'I', null
    FROM drLearningActivityTranscript uLat
    inner join drLearningActivityInstance LAI
    on ulat.learningActivityInstanceID=ulat.learningActiv ityInstanceID
    where ulat.id IN
    (select lat.id
    from drLearningActivityTranscript lat
    inner join drLearningActivityInstance Lai
    on lai.id=lat.learningActivityInstanceID
    where enrollmentstatus in ('E','W')
    and firstMEetingSTart > sysdate))
    and ulat.enrollmentStatus ='W'
    and firstMeetingSTart < sysdate;
    I've tweaked it some, but none of my tweaks work properly

    The query needs to update only the records for drLearningActivityTranscript where drLearningActivityTranscript .enrollmentStatus ='W'
    and drLearningActivityInstance.firstMeetingSTart < sysdate.

    Thanks,

    Robert

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    With a little formatting the typo jumps out at you:

    Code:
    UPDATE ulat
    SET    ( ulat.enrollmentstatus, seatallocationid ) =
           ( SELECT 'I', NULL
             FROM   drlearningactivitytranscript ulat
                    INNER JOIN drlearningactivityinstance lai
                    ON ulat.learningactivityinstanceid = ulat.learningactiv ityinstanceid
             WHERE  ulat.id IN
                    ( SELECT lat.id
                      FROM   drlearningactivitytranscript lat
                             INNER JOIN drlearningactivityinstance lai
                             ON lai.id = lat.learningactivityinstanceid
                      WHERE  enrollmentstatus IN ('E', 'W')
                      AND    firstmeetingstart > SYSDATE )
           )
    AND    ulat.enrollmentstatus = 'W'
    AND    firstmeetingstart < SYSDATE;

  7. #7
    Join Date
    May 2004
    Posts
    184
    William,

    I'm sorry I just don't see it. I see the bold AND at the end, and I would assume then that this needs to be a WHERE instead. However, if I change that, then UPDATE ulat gives me an error that the table 'ulat' doesn't exists.

    Sorry to be so dense.

    Robert


    Quote Originally Posted by WilliamR
    With a little formatting the typo jumps out at you:

    Code:
    UPDATE ulat
    SET    ( ulat.enrollmentstatus, seatallocationid ) =
           ( SELECT 'I', NULL
             FROM   drlearningactivitytranscript ulat
                    INNER JOIN drlearningactivityinstance lai
                    ON ulat.learningactivityinstanceid = ulat.learningactiv ityinstanceid
             WHERE  ulat.id IN
                    ( SELECT lat.id
                      FROM   drlearningactivitytranscript lat
                             INNER JOIN drlearningactivityinstance lai
                             ON lai.id = lat.learningactivityinstanceid
                      WHERE  enrollmentstatus IN ('E', 'W')
                      AND    firstmeetingstart > SYSDATE )
           )
    AND    ulat.enrollmentstatus = 'W'
    AND    firstmeetingstart < SYSDATE;

  8. #8
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by rtnetworks
    I remembered there was something weird about Oracle needing the parethesis and I tried a few variations, but missed where the parenthesis really needed to go. Thank you.

    So, that I understand this for the future. If I need to update a column and my underlying query has only one table, I don't need a subselect (unless of course the value comes from a subselect). On the other hand, if my underlying query contains more than one table, I must use parenthesis using the syntax of:

    Code:
    UPDATE table
    SET (fieldlist)=(underlying table and criteria)
    Is this correct?
    Er, I never heard the syntax described like that, but possibly.

    The simple form is just:

    Code:
    UPDATE tablename
    SET    col1 = expression1
         , col2 = expression2
    WHERE  limitingclause;
    If an expression needs to be a subquery, then so be it:

    Code:
    UPDATE tablename t
    SET    col1 = expression1
         , col2 =
           ( SELECT somecol
             FROM   othertab
             WHERE  key = t.key )
    WHERE  limitingclause;
    The query can be arbitrarily complex, so for example it could include further subqueries if you need them. The structure of the query is not relevant to the syntax of UPDATE.

    If you need to update col1 and col2 using values from the same query, you can avoid repeating the query by grouping them together:

    Code:
    UPDATE tablename t
    SET    ( col1, col2 ) =
           ( SELECT somecol1, somecol2
             FROM   othertab
             WHERE  key = t.key )
    WHERE  limitingclause;
    Going back to the first, simple case, note that [tablename] can be an updateable view, and views can be inline. So for example,

    Code:
    CREATE VIEW myview
    AS
    SELECT empno  AS employee_id
         , ename  AS employee_name
         , deptno AS department_id
    FROM   emp;
    
    UPDATE myview
    SET    employee_name = 'JONES'
    WHERE  employee_name = 'SMITH';
    ...is the same thing as...

    Code:
    UPDATE ( SELECT empno  AS employee_id
                  , ename  AS employee_name
                  , deptno AS department_id
             FROM   emp ) e
    SET    e.employee_name = 'JONES'
    WHERE  e.employee_name = 'SMITH';
    ...which allows you to update joins in a similar way to SQL Server:

    Code:
    UPDATE ( SELECT empno  AS employee_id
                  , ename  AS employee_name
                  , d.deptno AS department_id
                  , d.dname  AS department_name
             FROM   emp e1
                    JOIN dept d
                    ON   d.deptno = e1.deptno ) e
    SET    e.employee_name = 'JONES'
    WHERE  e.department_name = 'SALES';
    There are some restrictions on what views can be updateable. The above example is only allowed if d.deptno is defined unique via a constraint.

  9. #9
    Join Date
    May 2004
    Posts
    184

    Smile

    William,

    Thanks for taking the time to explain this. I know that was a lot of typing...

    So if I understand you correctly, I need to treat the FROM clause of my query like an inline view whenever my update_criteria involves more than one table. So my query would look like:
    Code:
    UPDATE 
    (SELECT *
            FROM drLearningActivityTranscript uLat 
                 inner join drLearningActivityInstance LAI  on lai.id=ulat.learningActivityInstanceID
            where ulat.id IN 
                  (select lat.id 
                   from drLearningActivityTranscript lat 
                        inner join drLearningActivityInstance Lai  on lai.id=lat.learningActivityInstanceID
                        where enrollmentstatus in ('E','W')
                        and firstMEetingSTart > sysdate)
                        and enrollmentStatus ='W' 
              and LAI.firstMeetingSTart < sysdate)
    SET enrollmentStatus='I', seatAllocationID=null
    Did I understand everything you said correctly?

    Thank you. And I will check out the link you sent as well.

    Robert

  10. #10
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by rtnetworks
    I'm sorry I just don't see it. I see the bold AND at the end, and I would assume then that this needs to be a WHERE instead. However, if I change that, then UPDATE ulat gives me an error that the table 'ulat' doesn't exists.
    No, you got it. The WHERE clause has to start with "WHERE". That's why the first AND was highlighted in the error message. Now you are on to another error.

    I didn't look at the actual update logic. I'm not sure I understand it, but I notice there are two "ulat"s in your query, and part of it seems to query the same rows twice. Perhaps this would work better (untested, wild guess):

    Code:
    UPDATE ulat u
    SET    ( enrollmentstatus, seatallocationid ) =
           ( SELECT 'I', NULL
             FROM   drlearningactivitytranscript lat
                    INNER JOIN drlearningactivityinstance lai
                    ON lai.learningactivityinstanceid = lat.learningactivityinstanceid
             WHERE  lat.id = u.lat_id )
    AND    u.enrollmentstatus = 'W'
    AND    u.firstmeetingstart < SYSDATE;
    or perhaps even

    Code:
    UPDATE ulat u
    SET    enrollmentstatus  ='I'
         , seatallocationid = NULL
    WHERE  EXISTS
           ( SELECT NULL
             FROM   drlearningactivitytranscript lat
                    INNER JOIN drlearningactivityinstance lai
                    ON lai.learningactivityinstanceid = lat.learningactivityinstanceid
             WHERE  lat.id = u.lat_id )
    AND    u.enrollmentstatus = 'W'
    AND    u.firstmeetingstart < SYSDATE;

  11. #11
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by rtnetworks
    So if I understand you correctly, I need to treat the FROM clause of my query like an inline view whenever my update_criteria involves more than one table.
    Correct. The word "UPDATE" has to be followed by either the literal name of a database object, or an equivalent inline view. You don't HAVE to use an inline view - it can be very efficient, but it is subject to some restrictions as I mentioned.

    Also I've just realised what the two "ulat" references were - the top one referred to the set defined in the join below, so my "UPDATE ulat" suggestions are no good. As you can tell I don't know MS SQL

    How about this:

    Code:
    UPDATE drlearningactivitytranscript lat
    SET    enrollmentstatus = 'I'
         , seatallocationid = NULL
    WHERE  EXISTS
           ( SELECT NULL
             FROM   drlearningactivityinstance lai
             WHERE  lai.learningactivityinstanceid = lat.learningactivityinstanceid )
    AND    lat.enrollmentstatus = 'W'
    AND    lat.firstmeetingstart < SYSDATE;
    Last edited by WilliamR; 11-24-05 at 15:26.

  12. #12
    Join Date
    May 2004
    Posts
    184
    [QUOTE=WilliamR]You don't HAVE to use an inline view[QUOTE]

    Unless of course there is a secondary table being used as part of your criteria, is that correct?

    Thank you again for your help. I actually did read through the UPDATE syntax prior to posting here, but I find the Oracle docs examples very simple compared to your explanation. Thank you!

    As you can tell I don't know MS SQL
    Well, as you can tell, I don't know Oracle very well either. Most of the syntax is similar or even identical, but the Oracle's UPDATE syntax is very different from any db syntax I've seen before. It makes much more sense now after your explanation. You're an MVP today!

    Don't know if you are in the US or not, but have a happy Thanksgiving today.

    Robert

  13. #13
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Well, if there are multiple tables involved you still have a choice between the updateable (inline) view approach and the regular "values from subquery" approach, e.g:

    Code:
    UPDATE sometable
    SET    somecolumn =
           ( correlated subquery )
    WHERE EXISTS
           ( similar subquery );
    or even a procedural approach via PL/SQL.

    What I was trying to say was that an Oracle inline view would be the equivalent of the alias in the MS SQL "UPDATE x FROM" syntax. There is no FROM in the Oracle version - you say what you are going to update right at the start.

    The updateable view approach still seems new to a lot of people as it only appeared 10 years ago in Oracle 7.2.

    Glad some of that helped anyway. Welcome to Oracle!
    Last edited by WilliamR; 11-24-05 at 19:44.

Posting Permissions

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