|
View previous topic :: View next topic
|
| Author |
Message |
mallik4u
New User
Joined: 17 Sep 2008
Posts: 75
Location: bangalore
|
|
|
|
Hi Friends,
It would be really great if some body help me to resolve the following problem.
I have two tables as follows
Table A
| Code: |
| ColA1,ColA2,ColA3,COLA4,COLA5 |
Table B
| Code: |
| ColB1,ColB2,ColB3,COLB4,COLB5 ...till COLB10 |
i need to write a delete query which should delete the records from TableA with the following conditions.
| Code: |
ColA1 = ColB1
ColA2 = ColB2
ColA3 = ColB3 |
I tried to use INNER JOIN but failed and got -199 SQLCODE for JOIN keyword.
Thanks & Regards,
Mallik |
|
| Back to top |
|
 |
anandinmainframe
Active User
Joined: 31 May 2007
Posts: 171
Location: India
|
|
|
|
Hi,
DELETE FROM Table A
where ColA1 = ColB1
and ColA2 = ColB2
and ColA3 = ColB3
will do a favour |
|
| Back to top |
|
|
mallik4u
New User
Joined: 17 Sep 2008
Posts: 75
Location: bangalore
|
|
|
|
Hi,
Thanks for the reply!!
But I have a question
How will you get ColB1, ColB2 and ColB3?
Regards,
Mallik |
|
| Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
|
|
|
|
[quote=malik]
I tried to use INNER JOIN but failed and got -199 SQLCODE for JOIN keyword.
[/quote]
Can you post your join query?
[quote=anand]
DELETE FROM Table A
where ColA1 = ColB1
and ColA2 = ColB2
and ColA3 = ColB3
will do a favour
[/quote]
will not work ColB1 ColB2 ColB3 are not actual values (literal here i mean)
It should be matched using join |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Anand,
The OP is trying to join Table A and Table B and it's not a single table. |
|
| Back to top |
|
|
mallik4u
New User
Joined: 17 Sep 2008
Posts: 75
Location: bangalore
|
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Mallik,
Thanks for posting the solution.  |
|
| Back to top |
|
|
anandinmainframe
Active User
Joined: 31 May 2007
Posts: 171
Location: India
|
|
|
|
| sorry i have forgetten that. |
|
| Back to top |
|
|
r2k1984
New User
.jpg)
Joined: 21 Jun 2005
Posts: 69
Location: chennai
|
|
|
|
The above query can be modified in to
DELETE FROM TableA ,TableB
WHERE (ColA1= ColB1,
ColA2=ColB2,
ColA3=COlB3); |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
| r2k1984 wrote: |
The above query can be modified in to
DELETE FROM TableA ,TableB
WHERE (ColA1= ColB1,
ColA2=ColB2,
ColA3=COlB3); |
This query does not work. That is the conclusion of this discussion.
The query which works has already been posted. |
|
| Back to top |
|
|
r2k1984
New User
Joined: 21 Jun 2005
Posts: 69
Location: chennai
|
|
|
|
Small modification to the query
DELETE FROM TableA ,TableB
WHERE (TABLEA.ColA1= TABLEA.ColB1,
TABLEA.ColA2=TABLEB.ColB2,
TABLEA.ColA3=TABLEB.COlB3);
This will surely works |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
| Did you test this query? |
|
| Back to top |
|
|
|
|
