12 Replies Latest reply on Nov 19, 2012 2:37 PM by user515689

    update statement in join query

    user515689
      Hi folks,

      I've seen this example numerous places, and tried to implement it, but I keep getting an "invalid identifier" error message, despite the fact that I've got the table and column specifically identified.

      For instance, my query reads like:
      UPDATE tbl1
      SET tbl1.EMPID =
      (SELECT  tbl2.EMPIDA  FROM tbl2  
      WHERE LOWER(tbl1.EMAILCOL) =  LOWER(tbl2.EMAILCOL2) 
      )
      WHERE tbl2.EMPIDA IN ('Z1O435','S8M4722','M0D5156')
      AND EXISTS 
      (SELECT tbl2.EMPIDA
       FROM tbl2 
       WHERE  tbl1.EMAILCOL= tbl2.EMAILCOL2 );
      But I'll keep getting flagged at the tbl2.EMPIDA column reference. Am I missing something here? Maybe it's obvious and I'm overlooking it, but I'm not sure. Any guidance would be appreciated!

      I have not tried this in SQL Plus, just in TOAD, but it seems to repeatedly fail.


      I have had to dump records to standalone Access tables and link back to perform the updates. It's quite frustrating.

      I am using Oracle 11, fwiw.

      Thanks.
        • 1. Re: update statement in join query
          lee200
          The problem is with this line:
          WHERE tbl2.EMPIDA IN ('Z1O435','S8M4722','M0D5156')
          It is referencing tbl2. Should it be tbl1?
          • 2. Re: update statement in join query
            Hoek
            Why not use MERGE here?
            http://www.oracle-base.com/articles/10g/merge-enhancements-10g.php
            • 3. Re: update statement in join query
              user515689
              I was starting to wonder if merge might not be better.

              I have about 100 or so records, in reality, that are incorrect in one table (1) and need the values from table 2 to overwrite them in their respective column.

              But overall, the table (table1) is about 20,000 records, so I thought merge might be a little overkill.
              • 4. Re: update statement in join query
                user515689
                No, lee, I don't think so. The values I need set are in table1. The correct values with which I need to update them with are in table 2.
                • 5. Re: update statement in join query
                  Hoek
                  I thought merge might be a little overkill.
                  What makes you think so?
                  20,000 records is peanuts, it's a very small number...nothing to worry about.
                  • 6. Re: update statement in join query
                    user515689
                    Well, probably not overkill in terms of what Oracle is capable of, just in terms of what I need, and I didn't think updating 20,000 records would really be necessary. Merge does indeed go ahead and update if it matches the main column indexed, correct?
                    I mean it doesn't skip over records matched, it just looks for one column that is matched, then bangs out the update, correct?

                    And I suppose I was wanting to specifically touch just the 100 records and not all 20,000. Whether it would be peanuts for Oracle to do or not.

                    Thanks!
                    • 7. Re: update statement in join query
                      Hoek
                      You can simply test it.
                      If you check the execution plan of your query then you know what Optimizer decided to do.
                      It is explained in the links that are referred to from here: {message:id=9360003}
                      Whether it would be peanuts for Oracle to do or not.
                      Yes, it would be peanuts.
                      • 8. Re: update statement in join query
                        AlbertoFaenza
                        Hi,

                        are you sure this query is valid?
                        Which is you database version?

                        I have tried the following:
                        CREATE TABLE tbl1
                        ( empid integer
                        , emailcol varchar2(20)
                        );
                        
                        create table tbl2
                        ( empida integer
                        , emailcol2 varchar2(20)
                        );
                        
                        UPDATE tbl1
                           SET tbl1.empid =
                                  (SELECT tbl2.empida
                                     FROM tbl2
                                    WHERE LOWER (tbl1.emailcol) = LOWER (tbl2.emailcol2))
                         WHERE tbl2.empida IN ('Z1O435', 'S8M4722', 'M0D5156')
                               AND EXISTS
                                      (SELECT tbl2.empida
                                         FROM tbl2
                                        WHERE tbl1.emailcol = tbl2.emailcol2);
                        
                        Error at line 1
                        ORA-00904: "TBL2"."EMPIDA": invalid identifier
                        The problem is related to this line:
                        tbl2.empida IN ('Z1O435', 'S8M4722', 'M0D5156')
                        despite you said it is not this. You cannot make a reference to tbl2 here, because tbl2 is used only in the 2 subqueries and not in the main query.

                        Beside this, you are using
                        WHERE LOWER (tbl1.emailcol) = LOWER (tbl2.emailcol2)
                        in the first subquery, but in the second subquery, the one used in the WHERE clasue you don't use the lower function
                        WHERE tbl1.emailcol = tbl2.emailcol2
                        So in case emailcol and emailcol2 are having different case you are not selecting these records for update.

                        Please post your table structure, some sample data and explain exactly how you want to update tbl1.

                        Regards.
                        Al

                        Edited by: Alberto Faenza on Nov 16, 2012 8:58 PM
                        1 person found this helpful
                        • 9. Re: update statement in join query
                          user515689
                          Thank you, Alberto, I will do. And I noted in the OP, it's Ora 11g. At least I believe I did.

                          Edited by: user515689 on Nov 16, 2012 1:27 PM
                          • 10. Re: update statement in join query
                            user515689
                            Alberto Faenza wrote:
                            Hi,

                            are you sure this query is valid?
                            Which is you database version?

                            I have tried the following:
                            CREATE TABLE tbl1
                            ( empid integer
                            , emailcol varchar2(20)
                            );
                            
                            create table tbl2
                            ( empida integer
                            , emailcol2 varchar2(20)
                            );
                            
                            UPDATE tbl1
                            SET tbl1.empid =
                            (SELECT tbl2.empida
                            FROM tbl2
                            WHERE LOWER (tbl1.emailcol) = LOWER (tbl2.emailcol2))
                            WHERE tbl2.empida IN ('Z1O435', 'S8M4722', 'M0D5156')
                            AND EXISTS
                            (SELECT tbl2.empida
                            FROM tbl2
                            WHERE tbl1.emailcol = tbl2.emailcol2);
                            
                            Error at line 1
                            ORA-00904: "TBL2"."EMPIDA": invalid identifier
                            The problem is related to this line:
                            tbl2.empida IN ('Z1O435', 'S8M4722', 'M0D5156')
                            despite you said it is not this. You cannot make a reference to tbl2 here, because tbl2 is used only in the 2 subqueries and not in the main query.>
                            Beside this, you are using
                            WHERE LOWER (tbl1.emailcol) = LOWER (tbl2.emailcol2)
                            in the first subquery, but in the second subquery, the one used in the WHERE clasue you don't use the lower function
                            WHERE tbl1.emailcol = tbl2.emailcol2
                            So in case emailcol and emailcol2 are having different case you are not selecting these records for update.

                            Please post your table structure, some sample data and explain exactly how you want to update tbl1.

                            Regards.
                            Al

                            Edited by: Alberto Faenza on Nov 16, 2012 8:58 PM
                            But Alberto, I just use table1 in the main query, because that is the update statement; I can't update more than one table in a statement, so isn't that the "main query" where as I reference table2 in the two subqueries? Or am I missing something there?

                            Thanks!
                            • 11. Re: update statement in join query
                              AlbertoFaenza
                              Hi,

                              the syntax you posted is wrong. That's it.

                              If you want to know how to write your update you should post some sample data and expected output.
                              I can post some query but don't know if it will work for your case.

                              As mentioned in SQL and PL/SQL FAQ you should always put table structure, sample data and expected output.

                              In the same link, it specified how to post an Oracle version
                              SELECT * FROM V$VERSION;
                              which is returning additional details.

                              Regarding your update the following statement are all valid in syntax but I don't know if they will update the records in the way you want or if you might have error as I don't know your table structure, primary key, etc.
                              -- method 1
                              UPDATE tbl1
                                 SET tbl1.empid =
                                        (SELECT tbl2.empida
                                           FROM tbl2
                                          WHERE LOWER (tbl1.emailcol) = LOWER (tbl2.emailcol2))
                               WHERE EXISTS (SELECT 1
                                               FROM tbl2
                                              WHERE LOWER (tbl1.emailcol) = LOWER (tbl2.emailcol2)
                                                AND tbl2.empida IN ('Z1O435', 'S8M4722', 'M0D5156'));
                              
                              -- method 2                
                              MERGE INTO tbl1
                                 USING (SELECT empida, emailcol2
                                           FROM tbl2
                                          WHERE empida IN ('Z1O435', 'S8M4722', 'M0D5156')
                                       ) tbl2  ON (LOWER (tbl1.emailcol) = LOWER (tbl2.emailcol2))
                               WHEN MATCHED THEN
                                  UPDATE SET tbl1.empid = tbl2.empida;
                              Regards.
                              Al
                              • 12. Re: update statement in join query
                                user515689
                                OK, thanks, Al!