4 Replies Latest reply: Mar 5, 2013 9:42 AM by user13397562 RSS

    Valid SQL?

    user13397562
      I have a SQL that works, but its kind of confusing the way it works. Below are the scripts.

      1.
      create table test_table (
      a number primary key,
      b Varchar2(5),
      c_subq_use Number,
      update_user_id varchar2(10) default null,
      update_date date default null);

      2.
      insert into test_table values (1,'XX',11,null,null);
      insert into test_table values (2,'YY',22,null,null);
      insert into test_table values (3,'ZZ',33,null,null);
      COMMIT;

      3.
      create table test_table_subq
      (a number);

      insert into test_table_subq values (1);

      COMMIT;

      -- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      Now if I run the below update statement the column C_SUBQ_USE=11 is actually present in the table 'test_table' and not ' test_table_subq'. The subquery as stand alone fails but the Query as a whole succeeded and rows are actually updated in the test_table table.

      update test_table   set a=1111, B='AAAA', UPDATE_USER_ID='Jackie', UPDATE_DATE=sysdate
      where A=(select a from test_table_subq  where a=1 and C_SUBQ_USE=11);

      --1 row updated
      Rollback;

      I am sure the column C_SUBQ_USE is being referenced from the table test_table , because if I alias the both the table its evident.

      update test_table outer set a=1111, B='AAAA', UPDATE_USER_ID='Jackie', UPDATE_DATE=sysdate
      where A=(select a from test_table_subq inner where a=1 and outer.C_SUBQ_USE=11);
      --1 row updated

      Rollback;


      update test_table outer set a=1111, B='AAAA', UPDATE_USER_ID='Jackie', UPDATE_DATE=sysdate
      where A=(select a from test_table_subq inner where a=1 and O.C_SUBQ_USE=11);

      Gives ---> ORA-00904: "O"."C_SUBQ_USE": invalid identifier


      Is this a correct way to write the SQL. Should'nt it be instead. What is the difference in both of these?

      update test_table   set a=1111, B='AAAA', UPDATE_USER_ID='Jackie', UPDATE_DATE=sysdate
      where A=(select a from test_table_subq  where a=1)  and C_SUBQ_USE=11;

      The predicate information from the explain plan as to how they operate is also quite different for both of them . the explain plan for the first update statement has the column C_SUBQ_USE=11 as binded.
        • 1. Re: Valid SQL?
          Balazs Papp
          update test_table outer* set a=1111, B='AAAA', UPDATE_USER_ID='Jackie', UPDATE_DATE=sysdate
          where A=(select a from test_table_subq inner where a=1 and O*.C_SUBQ_USE=11);

          what is O?
          • 2. Correlated Sub-Query
            Frank Kulash
            Hi,
            user13397562 wrote:
            I have a SQL that works, but its kind of confusing the way it works. Below are the scripts.

            1.
            create table test_table (
            a number primary key,
            b Varchar2(5),
            c_subq_use Number,
            update_user_id varchar2(10) default null,
            update_date date default null);

            2.
            insert into test_table values (1,'XX',11,null,null);
            insert into test_table values (2,'YY',22,null,null);
            insert into test_table values (3,'ZZ',33,null,null);
            COMMIT;

            3.
            create table test_table_subq
            (a number);

            insert into test_table_subq values (1);

            COMMIT;

            -- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

            Now if I run the below update statement the column C_SUBQ_USE=11 is actually present in the table 'test_table' and not ' test_table_subq'. The subquery as stand alone fails but the Query as a whole succeeded and rows are actually updated in the test_table table.

            update test_table   set a=1111, B='AAAA', UPDATE_USER_ID='Jackie', UPDATE_DATE=sysdate
            where A=(select a from test_table_subq  where a=1 and C_SUBQ_USE=11);
            You're doing a Correlated Sub-Query . The sub-query can reference any columns from the table in table of the main UPDATE statement (that is, test_table).
            --1 row updated
            Rollback;
            I am sure the column C_SUBQ_USE is being referenced from the table test_table , because if I alias the both the table its evident.

            update test_table outer set a=1111, B='AAAA', UPDATE_USER_ID='Jackie', UPDATE_DATE=sysdate
            where A=(select a from test_table_subq inner where a=1 and outer.C_SUBQ_USE=11);
            Exactly. If test_table_subq did have a column named c_subq_use, and you wanted to reference test_table.c_subq_use, then you would have to do it that way. But since c_subq_use only exists in one table, the compiler knows that when you say c_subq_use, you must mean outer.c_sub_q_use. It's a good practice to use aliases like this even when not strictly necessary, to reduce confusion for people reading and debugging the code.
            OUTER is actually an Oracle keyword. I suggest you use something like OUTER_TABLE, or O instead.
            --1 row updated

            Rollback;


            update test_table outer set a=1111, B='AAAA', UPDATE_USER_ID='Jackie', UPDATE_DATE=sysdate
            where A=(select a from test_table_subq inner where a=1 and O.C_SUBQ_USE=11);

            Gives ---> ORA-00904: "O"."C_SUBQ_USE": invalid identifier
            Right; there is no table O anywhere in the statement. The only table in scope at that point are outer and inner.
            Is this a correct way to write the SQL. Should'nt it be instead. What is the difference in both of these?

            update test_table   set a=1111, B='AAAA', UPDATE_USER_ID='Jackie', UPDATE_DATE=sysdate
            where A=(select a from test_table_subq  where a=1)  and C_SUBQ_USE=11;

            The predicate information from the explain plan as to how they operate is also quite different for both of them . the explain plan for the first update statement has the column C_SUBQ_USE=11 as binded.
            It will be more efficient to have conditions like <tt> C_SUBQ_USE=11 </tt> in the main UPDATE statement, and not in the sub-query. The optimizer will probably do that comparison first, and, if it fails, not bother evaluating the other condition.
            • 3. Re: Valid SQL?
              user13397562
              I am using O O.C_SUBQ_USE=11 in the query just to confirm that the column C_SUBQ_USE is being used from the outer query. Since none of the table objects has been aliased as 'O' (one was aliased as 'outer' and the other as 'inner') this query was intended to fail. Just wanted to make sure that the 'second test condition' in the subquery is not being ignored even when it is not a part of both the tables.
              • 4. Re: Valid SQL?
                user13397562
                Thanks Frank. I just had that hunch but I wanted to confirm from the forum. this was helpful .!