Home » SQL & PL/SQL » SQL & PL/SQL » bulk analytic update
bulk analytic update [message #197795] Thu, 12 October 2006 14:04 Go to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Hi Gurus

I have a table like this

Dept ID Dept Name Unique ID
1 A1
2 A1
3 A1
4 A2
5 A2
6 A2
7 A3
8 A4
9 A4
10 A4

Now I want my unique ID field to be generated like this

Dept ID Dept Name Unique ID
1 A1 123
2 A1 123
3 A1 123
4 A2 456
5 A2 456
6 A2 456
7 A3 124
8 A4 125
9 A4 125
10 A4 125

Also, the unique ID needs to be generated via an Oracle Sequence. The numbers given are just examples. i.e. unique ID takes values based on the Dept Name partition.

How to achieve this by an Update statement on the table? Please suggest a solution.

Raajesh

Re: bulk analytic update [message #197839 is a reply to message #197795] Thu, 12 October 2006 20:10 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Create a lookup table, then... well... look it up!

CREATE TABLE lkp AS
SELECT dept_name, my_seq.nextval AS uniq
FROM (
    SELECT distinct dept_name
    FROM dept
);

UPDATE dept
set uniq = (
  SELECT uniq 
  FROM lkp 
  WHERE lkp.dept_name = dept.dept_name
)


Ross Leishman
Re: bulk analytic update [message #197872 is a reply to message #197795] Fri, 13 October 2006 01:14 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Hi,

Is it not possible to do this without a look up table..?

The datamodel permits to use two more fields on the same table that can be partitioned on any column..

Is such a bulk update possible, if the two columns are carefully chosen?

If so, what should be the partition criteria..

Raajesh
Re: bulk analytic update [message #197893 is a reply to message #197872] Fri, 13 October 2006 03:04 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Analytic functions are only allowed in the SELECT clause of a SQL, not the SET clause of an UPDATE.

Say - for instance - you used the syntax:
UPDATE tab
SET col = (SELECT ... analytic func ....)

then the sub-query gets executed once (separately) for each row update. You want the analytic function to run once, and then feed the results back into the update of all rows. So clearly this wont work either.

So how about something like this?
update (
select group_col
, old_val
, min(myseq.nextval) over (partition by group_col)
from mytab
)
set old_val = new_val


ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
That's not going to work either.

I'm about stuck now. There are other possibilities with PL/SQL, but that's just getting stupid. Create a mapping table.

Ross Leishman
Re: bulk analytic update [message #197895 is a reply to message #197795] Fri, 13 October 2006 03:11 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
True. You are right. Since I am using this in a Migration, I dont want to use PL SQL. I would like to minimize that to a greater extent. If there are no other optinos, then Mapping table should be the ideal way out.Keep posted if you get any ideas..

Thanks!
Raajesh
Re: bulk analytic update [message #198102 is a reply to message #197895] Sat, 14 October 2006 21:18 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Had a brain-wave, but it didn't work Sad

MERGE
INTO dept d
USING (
  SELECT b.deptno, b.dname, a.uniq
  FROM (
    SELECT dname, id.nextval AS uniq, rownum as rn
    FROM (
      SELECT DISTINCT dname
      FROM   dept1
    )
  ) a
  JOIN dept1 b ON a.dname = b.dname
) u 
ON (u.deptno = d.deptno)
WHEN MATCHED THEN UPDATE
SET d.uniq = u.uniq
-- For 9i, use dummy INSERT clause
WHEN NOT MATCHED THEN INSERT (deptno, dept_name, uniq)
VALUES (u.deptno, u.dept_name, 1/0)

ORA-02287 sequence number not allowed here 


I don't get a good feeling about finding a work-around.

Ross Leishman
Re: bulk analytic update [message #198228 is a reply to message #198102] Mon, 16 October 2006 04:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you create a package it's fairly easy, but then it stops being a straight SQL solution, I suppose.

create table temp_dept(dept_id  number, dept_name  varchar2(10), dept_unq number);

create sequence temp_dept_seq;

insert into temp_dept (dept_id, dept_name) values (1, 'A1');
insert into temp_dept (dept_id, dept_name) values (2, 'A1');
insert into temp_dept (dept_id, dept_name) values (3, 'A1');
insert into temp_dept (dept_id, dept_name) values (4, 'A2');
insert into temp_dept (dept_id, dept_name) values (5, 'A2');
insert into temp_dept (dept_id, dept_name) values (6, 'A2');
insert into temp_dept (dept_id, dept_name) values (7, 'A3');
insert into temp_dept (dept_id, dept_name) values (8, 'A4');
insert into temp_dept (dept_id, dept_name) values (9, 'A4');
insert into temp_dept (dept_id, dept_name) values (10,'A4');


create or replace package test as
  function get_seq (p_dept_name in  varchar2) return number;
end;;
/

create or replace package body test as

  type seq_tab is table of number index by varchar2(30);
  t_seq     seq_Tab;

  function get_seq (p_dept_name in  varchar2) return number as
  v_return  number;
begin
  if t_seq.exists(p_dept_name) then
    null;
  else
    select temp_dept_seq.nextval
    into   t_seq(p_dept_name)
    from   dual;
  end if;
  
  v_return := t_seq(p_dept_name);
  
  return v_return;
end get_seq;
end test;;
/

update temp_dept t1
set dept_unq = test.get_seq(dept_name);
               
select * from temp_Dept;

   DEPT_ID DEPT_NAME    DEPT_UNQ
---------- ---------- ----------
         1 A1                 15
         2 A1                 15
         3 A1                 15
         4 A2                 16
         5 A2                 16
         6 A2                 16
         7 A3                 17
         8 A4                 18
         9 A4                 18
        10 A4                 18
Re: bulk analytic update [message #198308 is a reply to message #198228] Mon, 16 October 2006 09:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you can do it in two update statements...

update temp_dept t1
set t1.dept_unq = temp_dept_seq.nextval
where rowid = (select min(rowid) from temp_dept t2 where t1.dept_name = t2.dept_name);

update temp_dept t1
set dept_unq = (select max(dept_unq) from temp_dept t2 where t1.dept_name = t2.dept_name);
Re: bulk analytic update [message #198338 is a reply to message #197895] Mon, 16 October 2006 12:42 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
This should do it in one SQL command:

update table1 a set (dept_id, unique_id) = (
	select dept_id, r from (
		select dept_id,
			sum(case b.dept_name when b.prev_dept then 0 else 1 end) over (order by b.dept.id) r from (
				select dept_id, dept_name,
				lag(dept_name) over (order by dept_id) prev_dept from table1) b)
	where dept_id = a.dept_id)
;


[Updated on: Mon, 16 October 2006 12:59]

Report message to a moderator

Re: bulk analytic update [message #198374 is a reply to message #198338] Mon, 16 October 2006 21:20 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
ebrian wrote on Tue, 17 October 2006 03:42
This should do it in one SQL command:

update table1 a set (dept_id, unique_id) = (
	select dept_id, r from (
		select dept_id,
			sum(case b.dept_name when b.prev_dept then 0 else 1 end) over (order by b.dept.id) r from (
				select dept_id, dept_name,
				lag(dept_name) over (order by dept_id) prev_dept from table1) b)
	where dept_id = a.dept_id)
;




The problem with that one is it doesn't allocate from a sequence (which is the tricky bit).

I like JR's second solution - clever.

Ross Leishman
Re: bulk analytic update [message #198505 is a reply to message #198374] Tue, 17 October 2006 07:36 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If the OP is strictly looking for a unique id for the UNIQUE_ID then my code would provide that for them and preclude the use of a sequence. But as you pointed out...Requirements...Requirements... Razz
Re: bulk analytic update [message #198511 is a reply to message #198505] Tue, 17 October 2006 07:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you can do it in one update and a trigger

create or replace trigger temp_dept_au_trg
after update on temp_dept
declare
  v_count  pls_integer;
begin
  select count(*) into v_count from temp_dept where dept_unq is null;
  
  if v_count > 0 then
    update temp_dept t1
    set dept_unq = (select max(dept_unq) from temp_dept t2 where t1.dept_name = t2.dept_name);
  end if;
end;
/

update temp_dept t1
set t1.dept_unq = temp_dept_seq.nextval
where rowid = (select min(rowid) from temp_dept t2 where t1.dept_name = t2.dept_name);


[edited to remove stupid double ; that SQL developer requires if you create triggers/procedures from the sql window]

[Updated on: Tue, 17 October 2006 07:50]

Report message to a moderator

Re: bulk analytic update [message #199554 is a reply to message #197795] Wed, 25 October 2006 01:53 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
I'm bit of struck now..

I tried the lookup table approach suggested earlier..It worked out well..The two update statement approach also seems to be fine..

On a performance front,where I expect more than a million rows in the table which one would be better?
Re: bulk analytic update [message #199577 is a reply to message #199554] Wed, 25 October 2006 02:52 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you create the lookup table as an IOT, it should be faster than the two-update approach.

However if you index DEPT_NAME, DEPT_UNQ and ensure that the index is analyzed, you may find that the 2-update approach is comparable.

Ross Leishman
Re: bulk analytic update [message #200030 is a reply to message #199577] Fri, 27 October 2006 08:49 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
check this out...

SQL> create table test (dept number, dept_name varchar2(10), unique_id number)
2 /

Table created.

SQL> insert into test (dept,dept_name) values (1,'A1')
2 /

1 row created.

SQL> insert into test (dept,dept_name) values (2,'A1')
2 /

1 row created.

SQL> insert into test (dept,dept_name) values (3,'A1')
2 /

1 row created.

SQL> insert into test (dept,dept_name) values (4,'A2')
2 /

1 row created.

SQL> insert into test (dept,dept_name) values (5,'A2')
2 /

1 row created.

SQL> insert into test (dept,dept_name) values (6,'A2')
2 /

1 row created.

SQL> insert into test (dept,dept_name) values (7,'A3')
2 /

1 row created.

SQL> insert into test (dept,dept_name) values (8,'A4')
2 /

1 row created.

SQL> insert into test (dept,dept_name) values (9,'A4')
2 /

1 row created.

SQL> insert into test (dept,dept_name) values (10,'A4')
2 /

1 row created.

SQL> commit
2 /

Commit complete.

SQL> select * from test
2 /

DEPT DEPT_NAME UNIQUE_ID
---------- ---------- ----------
1 A1
2 A1
3 A1
4 A2
5 A2
6 A2
7 A3
8 A4
9 A4
10 A4

10 rows selected.

SQL> drop sequence seq_test
2 /

Sequence dropped.

SQL> create sequence seq_test start with 1 increment by 1 nocycle
2 /

Sequence created.

var a number;
declare
b number;
i number;
begin
select nvl(max(unique_id),0) into b from test;
select seq_test.nextval - 1 into :a from dual;
for i in :a .. b - 1
loop
select seq_test.nextval - 1 into :a from dual;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> merge into test
2 using
3 (select dept_name, rownum + :a as rno from (select distinct dept_name from dept where unique_id is null order by dept_name)) b
4 on (test.dept_name = b.dept_name)
5 when matched then update set test.unique_id = b.rno
6 when not matched then insert (test.dept,test.dept_name) values (NULL,NULL)
7 /

11 rows merged.

SQL> select * from test
2 /

DEPT DEPT_NAME UNIQUE_ID
---------- ---------- ----------
1 A1 1
2 A1 1
3 A1 1
4 A2 2
5 A2 2
6 A2 2
7 A3 3
8 A4 4
9 A4 4
10 A4 4


11 rows selected.

SQL>

[Updated on: Fri, 27 October 2006 09:40]

Report message to a moderator

Previous Topic: deleteing duplicates
Next Topic: chekcing the length of a LONG field
Goto Forum:
  


Current Time: Sat Dec 03 22:10:57 CST 2016

Total time taken to generate the page: 0.07562 seconds