Friday, February 24, 2012

delete rows by checking conditions in more than one column

Hi All,

I have tables in three hierarchy...
I need to delete rows from the table which in 3rd level of hierarchy.

For ex: Table 1 has primary key col1.
Table 2 has primary key Col2 and Foreign key COL1 and
Table 3 has primary key Col3 and Foregn key Col2.

Now I have to delete all the rows in table three where Table3.Col2=Table2.Col2
and Table2.Col1=Table1.Col1

I could insert successfully using this condition but I am unable to delete.
I tried nested queries but it fails as the sub query returns more than one row.

Please help me how to overcome this.
An example is more helpful.

Thank you
Madhavi

Quote:

Originally Posted by madhavi123

Hi All,

I have tables in three hierarchy...
I need to delete rows from the table which in 3rd level of hierarchy.

For ex: Table 1 has primary key col1.
Table 2 has primary key Col2 and Foreign key COL1 and
Table 3 has primary key Col3 and Foregn key Col2.

Now I have to delete all the rows in table three where Table3.Col2=Table2.Col2
and Table2.Col1=Table1.Col1

I could insert successfully using this condition but I am unable to delete.
I tried nested queries but it fails as the sub query returns more than one row.

Please help me how to overcome this.
An example is more helpful.

Thank you
Madhavi


Please don't post questions in the articles section.
Moved to the forum.

No comments:

Post a Comment