I have 300k of data in a table. How do I update the data equally to 10 different regions-CodePudding

I have 300k of data in excel which has region column , (30k data each region).I will import ttabhe file to table and update data in each region from 1-10 equally.

Update table_name set region=region1 where createddate=(select * from table_name order by createddate desc where rownum=30000)

Do I need to write update statements for every region taking count based on created date column ?

CodePudding user response：

If you have exactly 300k rows and you just want to update the regions without any particular ordering then you can use MERGE and correlate on the ROWID pseudo-column:

MERGE INTO table_name dst
USING (
  SELECT ROWID rid, CEIL(ROWNUM/30000) AS region
  FROM   table_name 
) src
ON (src.rid = dst.ROWID)
WHEN MATCHED THEN
  UPDATE SET region = src.region;

If you want to use NTILE (with any size data set) then:

MERGE INTO table_name dst
USING (
  SELECT ROWID rid, NTILE(10) OVER (ORDER BY ROWNUM) AS region
  FROM   table_name 
) src
ON (src.rid = dst.ROWID)
WHEN MATCHED THEN
  UPDATE SET region = src.region;

CodePudding user response：

I guess ntile might help here.

Here's an example; I don't have that large table, so I'll mimic it using Scott's sample schema. I'll uniformly split rows into 4 regions (10 are a little bit too much for such a small table).

Sample data:

SQL> select * From test order by region, deptno, empno;

    REGION     DEPTNO      EMPNO ENAME                JOB
---------- ---------- ---------- -------------------- --------------------
                   10       7782 CLARK                MANAGER
                   10       7839 KING                 PRESIDENT
                   10       7934 MILLER               CLERK
                   20       7369 SMITH                CLERK
                   20       7566 JONES                MANAGER
                   20       7788 SCOTT                ANALYST
                   20       7876 ADAMS                CLERK
                   20       7902 FORD                 ANALYST
                   30       7499 ALLEN                SALESMAN
                   30       7521 WARD                 SALESMAN
                   30       7654 MARTIN               SALESMAN
                   30       7698 BLAKE                MANAGER
                   30       7844 TURNER               SALESMAN
                   30       7900 JAMES                CLERK

14 rows selected.

Set regions (you'd use ntile(10) in your case):

SQL> merge into test a
  2    using (select t.empno,
  3                 ntile(4) over (order by t.deptno, t.empno) region
  4           from test t
  5          ) b
  6    on (a.empno = b.empno)
  7    when matched then update set
  8      a.region = b.region;

14 rows merged.

Result:

SQL> select * From test order by region;

    REGION     DEPTNO      EMPNO ENAME                JOB
---------- ---------- ---------- -------------------- --------------------
         1         20       7369 SMITH                CLERK
         1         10       7782 CLARK                MANAGER
         1         10       7839 KING                 PRESIDENT
         1         10       7934 MILLER               CLERK
         2         20       7902 FORD                 ANALYST
         2         20       7566 JONES                MANAGER
         2         20       7876 ADAMS                CLERK
         2         20       7788 SCOTT                ANALYST
         3         30       7499 ALLEN                SALESMAN
         3         30       7521 WARD                 SALESMAN
         3         30       7654 MARTIN               SALESMAN
         4         30       7844 TURNER               SALESMAN
         4         30       7900 JAMES                CLERK
         4         30       7698 BLAKE                MANAGER

14 rows selected.

SQL>