1 Reply Latest reply on Jul 17, 2014 11:44 PM by rp0428

    issue when cleaning up tables

    Steve C.

      I was cleaning up a couple of tables, manually deleting rows from a header and master table when I typoed something and almost cleared the table.

       

      1) I have a temp table:

      create global temporary table TempMiscKeys(Key1 varchar2(50)) on commit preserve rows;

       

      2) I have 2 tables, header and detail:

        headerTable - key column = ProcessId giud (also has an AddDate column)

        detailTable  - key column ProcessId and column1

       

      3) I insert the rows to delete into the temp table:

         insert into TempMiscKeys (select ProcessId from HeaderTable where adddate < sysdate - 100);

       

      4) When I created my delete statement I typoed:

        was:  delete from DetailTable where ProcessId in (select ProcessId from TempMiscKeys);

      should be:  delete from DetailTable where ProcessId in (select Key1 from TempMiscKeys);

       

      What this did is deleted all rows from the DetailTable. I wouldn't think that 'select ProcessId from TempMiscKeys' would work since ProcessId isn't a column in the temp table.

       

      Steve

        • 1. Re: issue when cleaning up tables

          This question has NOTHING to do with sql developer. This forum, as the title says, is for Sql Developer questions only.

           

          But since it is easily answered I will do so and then ask you to mark the thread ANSWERED and repost in the SQL and PL/SQL forum if you need any further help.

          https://community.oracle.com/community/developer/english/oracle_database/sql_and_pl_sql

          What this did is deleted all rows from the DetailTable.

          Correct - that is pretty much what this query tells it to do:

          delete from DetailTable where ProcessId in (select ProcessId from TempMiscKeys);

          You are using an uncorreleated nested subquery. so ANY column reference in the subquery SELECT clause that can't be resolved by the 'TempMiscKeys' table is interpreted as a column in one of the tables from the outer query. In this case you only have ONE table in the outer query so that one ('DetailTable') is used.

           

          So your query essentially says:

          1. process every row in 'DetailTable'

          2. construct an IN list by taking the current rows value of 'ProcessId' and do a cartesian join with the every row in the TempMiscKeys table.

          3. delete the current row if the value IS IN THE LIST - which it, of course will be

           

          If your temp table has 100 rows step 2 will create 100 rows that each have the value of 'ProcessId' for the current detail row.

          I wouldn't think that 'select ProcessId from TempMiscKeys' would work since ProcessId isn't a column in the temp table.

          Surprise! That's how subqueries work. How else would the subquery refer to columns from the tables in the outer query?

           

          Please don't post any followup in this forum.