2 Replies Latest reply on Nov 3, 2015 2:18 AM by rp0428

    Delete Multiple Entries, Large Database

    93851a91-9b71-4ad7-945e-1b80663d317c

      Hi All,

       

      Pretty new to all of this so hoping someone can help...

       

      I have a large database, 4.5m rows of data in a table. The issue i have is this was created from c170 individual .csv files merged together, so there are multiple header rows as individual row entries, so essentially the row values match the headers. The table and columns are:

       

      TABLE "SYS"."A_BOOKINGS_DATA"

                     "SITENAME" VARCHAR2(100 BYTE),

                     "NETWORKID" VARCHAR2(10 BYTE),

                    "CUSTOMERID" NUMBER,

                     "BOOKINGDATETIME" VARCHAR2(20 BYTE),

                     "VISITDATETIME" VARCHAR2(20 BYTE),

                     "BOOKINGSTATUS" VARCHAR2(20 BYTE),

                     "BOOKINGSOURCE" VARCHAR2(100 BYTE),

                     "PRODUCT" VARCHAR2(150 BYTE),

                     "SESSION_DETAIL" VARCHAR2(50 BYTE),

                     "TOTALORDERS" NUMBER,

                     "BOOKINGID" VARCHAR2(25 BYTE),

                     "CLIENTBILLINGREFERENCE" VARCHAR2(40 BYTE),

                     "TITLE" VARCHAR2(20 BYTE),

                     "FIRSTNAME" VARCHAR2(50 BYTE),

                     "LASTNAME" VARCHAR2(50 BYTE),

       

      I recognized this issue as the CUSTOMERID has row entries with non-numeric values.

       

      What sort of query could i use to drop these rows, as i am struggling to write something that works, once this is done it's the bulk of what i need doing!

       

      Many thanks in advance for anyone who could help.

        • 1. Re: Delete Multiple Entries, Large Database
          thatJeffSmith-Oracle

          Try the SQL space, you'll get better answers there. This space is for the tooling itself, like how to use SQL Developer run said queries.

          • 2. Re: Delete Multiple Entries, Large Database

            I suggest you double-check what you posted.

            TABLE "SYS"."A_BOOKINGS_DATA"

                           "SITENAME" VARCHAR2(100 BYTE),

                           "NETWORKID" VARCHAR2(10 BYTE),

                          "CUSTOMERID" NUMBER,

            You should NEVER create or alter objects in the SYS schema. Recreate that table in a user schema and drop the one in the SYS schema.

             

            That is a proven way to cause database corruption and problems.

            I recognized this issue as the CUSTOMERID has row entries with non-numeric values.

            It is NOT possible for an Oracle column defined as NUMBER to contain data with 'non-numeric values'.

             

            So that data could NOT have been loaded into that table; the BAD DATA can only exist in your merged csv file so you will either need to recreate that file or use an editor to remove the multiple header rows.

             

            Since the bad data can NOT exist in that column in the table I'm not sure how posting in the SQL forum can help.

             

            But if you do have a SQL question that is the forum to post it in.