eliminate a table

[7280]

Hi i have two tables which are joined by a column id.
Table A is like this:
sequence
id
cod_a
cod_b
cod_c
notes

Table B:
sequence
id
..
...
cod_channel

What i want to do is add a new column cod_channel in table a which will contain data of table b. Then drop table b because all information are useless unless cod_channel column.
How can this be done? As i told they are related by id.
I need to update table A but i don't know how
Please help

 

[nread]

ALTER TABLE tableA
ADD (cod_channel Varhcar(??));

INSERT INTO tableA (A.id,A.cod_a,A.cod_b,A.cod_c,A.notes,B.cod_channel)
SELECT id,cod_a,cod_b,cod_c,notes,cod_channel
FROM tableA A,tableB B
WHERE A.id = B.id;

 

[7280]

No... it's not an insert.. it's an update of old rows..
now for those rows i have to insert in the new column it's corresponding value of table b

 

[sem]

If you have strict one-to-one relation you may use

update a set cod_channel = (select cod_channel from b where b.id = a.id);

If you have one-to-null or one-to-many relations, you need some agregate function to avoid sql errors e.g.

update a set cod_channel = (select max(cod_channel) from b where b.id = a.id);


Regards, Dima

 

[7280]

i already wrote first query but it failed with "ORA-01427: single-row subquery returns more than one row" error.
I will try with max clause.
But in this way i think it will update one row per time and the table is 7.000.000 records!! so for sure i have to issue
a very performing query (don't want to have rollback problems) and maybe i have to write a pl/sql block for the update.

 

[7280]

and cod_channel is a foregn key to another table and it contains only 4 numeric values.

 

[sem]

I've explained this behaviour above: you definitely have more than 1 record in table B, corresponding to the record in A. So, you should use some agregation (I don't know about your specific case, use max or min or any single-row function).
Yes, you need to update all 7000000 records. You may do it in a number of calls commiting after each bunch:

for i in 0..700
update a set cod_channel =
(select cod_channel from b where b.id = a.id)
where id between i*1000+1 and (i+1)*1000;
commit;
end loop;

Of course, this assumes that your id is between 1 and 7000000, make changes appropriately.

Regards, Dima

 

[sybaseguru]

Just a small correction to Dima's code

for i in 0..700
LOOP  -- loop is needed
  update a set cod_channel = 
  (select   cod_channel from b where b.id = a.id)
   where id between i*1000+1 and (i+1)*1000;
   commit;
end loop;

 

[sybaseguru]

Sorry a bit of mistake, just remembered for id between 1 until 7,000,000 you should do

for i in 1..7000
LOOP
 update a set cod_channel = 
  (select cod_channel from b where b.id = a.id)
   where id 
          between (i-1)*1000+1 and i*1000;
   commit;
end loop;

 

[sem]

Yes, 2 errors in 6 lines - higher than normal level

Regards, Dima

 

[7280]

thanks for your help but i really don't know how to face the problem..
the two tables are related with a varchar2 column... it contain values like this 0001360744~000226 and the last part is progressive (000226-000227-000228)..
unfortunately it's not a sequence.. it's something like a "manual sequence".. before any insert my great developers select max of this value and then insert the next number!!

another thing... in the for can i specify something like 1..(select count(*) from a).. because table is continuosly used...
thanks again..really appreciate help

 

[sem]

Oh, you may have locking problems.
As for specifying "flexible" limit - oracle maintains read-consistency, thus once cursor is opened the data returned is not subjected to change.

Regards, Dima

 

[7280]

What do you think about updating the tables through a view?
UPDATE
(SELECT b.cod_channel bcod, a.cod_channel acod
FROM a, b
WHERE a.id=b.id)
SET acod=bcod;

It seems good.. right?
I had this error
"ORA-01779: cannot modify a column which maps to a non key-preserved table" but it's because i'm missing a primary key. After creating them i think it will be ok..
The only issue i was thinking of is performance.
Both tables are 7.000.000 records

 

[sem]

Views in Oracle can not be updated (with the rare obvious exception of single-table selects). They may be updated using INSTEAD OF triggers only. Thus you can not get better performance with view.

Regards, Dima

 

[7280]

oopss... it can't be updated.. it's not key-preserved
definitely i have to find another way!!

 

[a199664]

Have you concidered breaking this into steps. Build temp table with ID and MAX, MIN or whatever single row COD_CHANNEL. Then insert from the temp table.

CREATE TABLE BTEMP AS
(SELECT B.ID,MAX(COD_CHANNEL)
FROM TableB B
GROUP BY B.ID);

UPDATE A SET COD_CHANNEL = (SELECT T.COD_CHANNEL FROM BTEMP T WHERE T.ID = A.ID);

DROP TABLE BTEMP;