Topic: Database Administration >> How to delete rows which are common in all the tables?
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: How to delete rows which are common in all the tables? |
| vikhardba |
Posted: Apr 25, 2008 02:39:51 AM |
Total Post: 86
Joined: Apr, 2007
|
Dear All,
How to delete rows which are common in all the tables?
Sample :
Column name = SN
T1 contains SN as column
T2 contains SN as column
T3 contains SN as column
select table_name,column_name from user_tab_columns where column_name='SN';
Above query will return the tables which contains the specified column i.e; SN.
Rows SN01, SN02, SN03 and so on are contained in all the SN > T1, T2 and T3...
How do i delete this rows? Please help me.
Thanks in-advance.
|
|
|
 GOPU |
| Posted: Apr 25, 2008 04:35:45 AM |  |
|
Total Post: 334
Joined: Apr, 2008
|
Hi friend
plsease go through this
Hi,
The on delete cascade option is part of the constraint definition, not the delete command - here is a paste from the Oracle documentation (V7 unfortunately - the only one I have handy at the mo :( ), but it should apply equally to Oracle 8...
-----------------
Maintaining Referential Integrity with the ON DELETE CASCADE Option
If you use the ON DELETE CASCADE option, Oracle7 permits deletions of referenced key values in the parent table and automatically deletes dependent rows in the child table to maintain referential integrity.
Example VII
This example creates the EMP table, defines and enables the referential integrity constraint FK_DEPTNO, and uses the ON DELETE CASCADE option:
CREATE TABLE emp
(empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2) CONSTRAINT fk_deptno
REFERENCES dept(deptno)
ON DELETE CASCADE )
Because of the ON DELETE CASCADE option, Oracle7 cascades any deletion of a DEPTNO value in the DEPT table to the DEPTNO values of its dependent rows of the EMP table. For example, if department 20 is deleted from the DEPT table, Oracle7 deletes the department's employees from the EMP table.
Regards
Gopu
|
|
|
|
|
| GOPU |
| Posted: Apr 25, 2008 04:38:13 AM | |
|
Total Post: 334
Joined: Apr, 2008
|
continuing from the above post..
example..demo..
SQL> create table test16 (id number primary key,name varchar2(10));
Table created.
SQL> create table test17 (id number,address varchar2(10), foreign key (id) references test16(id)
on
2 delete cascade);
These are the values..
SQL> select * from test16
2 ;
ID NAME
---------- ----------
100 gopu
200 ram
300 sam
SQL> select * from test17
2 ;
ID ADDRESS
---------- ----------
100 add1
200 add2
300 add3
SQL> delete from test16 where id=300;
1 row deleted.
SQL>
SQL> select * from test17;
ID ADDRESS
---------- ----------
100 add1
200 add2
SQL> select * from test16;
ID NAME
---------- ----------
100 gopu
200 ram
SQL>
I think you got some point...
Regards
Gopu
|
|
|
|
|
| vikhardba |
| Posted: Apr 25, 2008 05:04:29 AM | |
|
Total Post: 86
Joined: Apr, 2007
|
Thanks for the speedy response. My question is totally different from your answer. If possible please go through my post once again.
They are almost 50 tables which are having 'SN' as column and i need to delete rows which contains this SN column.
|
|
|
|
|
| Murtuja |
| Posted: Apr 26, 2008 09:10:46 AM | |
|
Total Post: 857
Joined: Jan, 2006
|
Hi Vikar,
Can you explain your requirement in more details ?
|
|
|
|
|
| MOH_DBA |
| Posted: Apr 27, 2008 02:27:00 PM | |
|
Total Post: 161
Joined: Nov, 2007
|
You Can do it in tow ways:
1)create a script that delete these in this table. do the following:
sql>set heading off
sql>spool C:\delete_tables.sql
sql>select 'delete ' || table_name || ';' from user_tab_columns where column_name='SN';
sql>spool off
sql>@C:\delete_tables.sql
sql>commit;
2)Using execute immediate;
begin
for x in (select table_name from user_tab_columns where column_name='SN') loop
execute immediate('delete ' || x.table_name);
end loop;
end;
/
commit;
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gopu Gopi | 334 |
| Gitesh Trivedi | 322 |
| Vinoth Kumar | 264 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
