Topic: SQL*Plus and PL/SQL >> 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:20:57 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:40:27 AM |  |
|
Total Post: 333
Joined: Apr, 2008
|
Hi,
Please dont post the same quesion in different topics,It wont help the peoples those who are helping you
Regards
Gopu
|
|
|
|
|
| vikhardba |
| Posted: Apr 26, 2008 12:04:08 AM | |
|
Total Post: 86
Joined: Apr, 2007
|
Yes, i agree with you. It has happened by mistake. Sorry to all :-(
|
|
|
|
|
| Vijay |
| Posted: Apr 29, 2008 03:38:46 AM | |
|
Total Post: 12
Joined: Jul, 2007
|
Dear Vikhar,
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.
---------------------------------------------------------------------------
To achieve this you can execute following procedure
DECLARE
V_SN_VALUE T1.SN%TYPE := NULL --Value which you want to delete from all tables.
V_SQL VARCHAR2(1000) := NULL;
BEGIN
FOR CUR IN (select table_name,column_name from user_tab_columns where column_name='SN')
LOOP --Loop executes once for each table
V_SQL := 'DELETE FROM ' || CUR.TABLE_NAME || ' WHERE ' || CUR.COLUMN_NAME || '=''' || V_SN_VALUE || ''''; --ASSUMING THE VARIABLE IS VARCHAR2 TYPE
EXECUTE IMMEDIATE V_SQL;
END LOOP;
END;
|
|
|
|
|
| Vijay |
| Posted: Apr 29, 2008 03:39:11 AM | |
|
Total Post: 12
Joined: Jul, 2007
|
Dear Vikhar,
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.
---------------------------------------------------------------------------
To achieve this you can execute following procedure
DECLARE
V_SN_VALUE T1.SN%TYPE := NULL --Value which you want to delete from all tables.
V_SQL VARCHAR2(1000) := NULL;
BEGIN
FOR CUR IN (select table_name,column_name from user_tab_columns where column_name='SN')
LOOP --Loop executes once for each table
V_SQL := 'DELETE FROM ' || CUR.TABLE_NAME || ' WHERE ' || CUR.COLUMN_NAME || '=''' || V_SN_VALUE || ''''; --ASSUMING THE VARIABLE IS VARCHAR2 TYPE
EXECUTE IMMEDIATE V_SQL;
END LOOP;
END;
Regards,
Vijay Mahawar
OCA.(Pursuing OCP)
|
|
|
|
|
| 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 | 333 |
| Gitesh Trivedi | 322 |
| Vinoth Kumar | 264 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
