Home » SQL & PL/SQL » SQL & PL/SQL » How to delete records based on two tables using join without sub query?
How to delete records based on two tables using join without sub query? [message #253231] Mon, 23 July 2007 04:05 Go to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Hi,

How to delete records based on two tables using join without sub query.

Table-1
--------
A1       B1
-----------
a1      1
a1      2
a1      3
a2      3
a2      5

Table-2
--------
A2       B2
-----------
a1      2
a1      3
a2      3
a2      5



Query that i have used :

DELETE FROM table-1
     WHERE table-1.A1 IN (SELECT table-2.A2
                               FROM table-2
                              WHERE table-1.A1 = table-2.A2 AND
                                    table-1.B1 = table-2.B2);


The things is i should not use sub query.

Can u plz help me in this reg?

[Updated on: Mon, 23 July 2007 05:23]

Report message to a moderator

Re: How to delete records based on two tables using join without sub query? [message #253232 is a reply to message #253231] Mon, 23 July 2007 04:18 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
I think there is No column having name A2,B2 in table-2...is your meant C and D?

--Yash
Re: How to delete records based on two tables using join without sub query? [message #253234 is a reply to message #253231] Mon, 23 July 2007 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, if you give aliases to the table, you have to then use the aliases and not the table name in the clauses.

Regards
Michel
Re: How to delete records based on two tables using join without sub query? [message #253261 is a reply to message #253234] Mon, 23 July 2007 05:24 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Sorry yaar. Now i have changed accordingly...
Re: How to delete records based on two tables using join without sub query? [message #253388 is a reply to message #253261] Mon, 23 July 2007 13:43 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Now then, next question is why you can't or don't want to use subqueries?
Re: How to delete records based on two tables using join without sub query? [message #253389 is a reply to message #253388] Mon, 23 July 2007 13:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Surely one of those stupid interview questions.
Instead of asking what is a bind variable or why do you have to prepare statements.

Regards
Michel
Re: How to delete records based on two tables using join without sub query? [message #253604 is a reply to message #253389] Tue, 24 July 2007 06:54 Go to previous messageGo to next message
tarunj
Messages: 23
Registered: April 2007
Location: Noida
Junior Member
TRY THIS
DELETE t1.* FROM table-1 t1, table-2 t2
WHERE t1.A1 = t2.A2 AND t1.B1 = t2.B2;
Re: How to delete records based on two tables using join without sub query? [message #253608 is a reply to message #253604] Tue, 24 July 2007 07:05 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> delete t1.* from t1, t2
  2  WHERE t1.A1 = t2.A2 AND t1.B1 = t2.B2;
delete t1.* from t1, t2
          *
ERROR at line 1:
ORA-00903: invalid table name

Please post something that is syntaxically valid.
Just a look at the DELETE statement would show you this is wrong.

Correct syntax is:
delete (select t1.* from t1, t2 WHERE t1.A1 = t2.A2 AND t1.B1 = t2.B2);

But
SQL> delete (select t1.* from t1, t2 WHERE t1.A1 = t2.A2 AND t1.B1 = t2.B2);
delete (select t1.* from t1, t2 WHERE t1.A1 = t2.A2 AND t1.B1 = t2.B2)
       *
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table

You must have primary key on condition:
SQL> alter table t1 add primary key (a1,b1);

Table altered.

SQL> delete (select t1.* from t1, t2 WHERE t1.A1 = t2.A2 AND t1.B1 = t2.B2);

0 rows deleted.

Regards
Michel
Previous Topic: how to put create procedure script inside logic block
Next Topic: VSIZE()
Goto Forum:
  


Current Time: Fri Dec 09 05:28:56 CST 2016

Total time taken to generate the page: 0.07542 seconds