Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Deleting rows from multiple tables

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vighnesh_r

New User


Joined: 23 Aug 2006
Posts: 14

PostPosted: Mon Apr 09, 2007 5:44 pm    Post subject: Deleting rows from multiple tables
Reply with quote

Suppose there are 2 tables A and B. Can i delete a particular row corresponding to a value from both tables using a single query ???

P.S. don't need delete cascade as it will delete rows from all tables(even the ones from which i don't want to delete it ) icon_confused.gif


TIA
Back to top
View user's profile Send private message

wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Tue Apr 10, 2007 12:57 am    Post subject: Re: Deleting rows from multiple tables
Reply with quote

I don't think this is possible. Could you explain why you can't use two separate statements to delete from two different tables?
Back to top
View user's profile Send private message
ragganga

New User


Joined: 22 Jan 2007
Posts: 18
Location: bangalore

PostPosted: Tue Apr 10, 2007 3:34 pm    Post subject:
Reply with quote

Hi Vighnesh,

Using Subquery it is possible.

Regards
Raghu
Back to top
View user's profile Send private message
Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Wed Apr 11, 2007 1:35 pm    Post subject:
Reply with quote

To add to what raghu told, you can have an exist check in where clause with a correlted subquery so that exist returns true only when you find a condition match in you table B.
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Thu Apr 12, 2007 5:48 am    Post subject:
Reply with quote

Prajesh_v_p wrote:
To add to what raghu told, you can have an exist check in where clause with a correlted subquery so that exist returns true only when you find a condition match in you table B.


He wants the same query to delete the row from table B as well. Could you please provide a sample query that will do what you just said?

thanks!
Back to top
View user's profile Send private message
ragganga

New User


Joined: 22 Jan 2007
Posts: 18
Location: bangalore

PostPosted: Thu Apr 12, 2007 12:28 pm    Post subject: Re: Deleting rows from multiple tables
Reply with quote

Hi Wanderer,

Have you read question properly what vighnesh asked

Can i delete a particular row corresponding to a value from both tables using a single query ???

To my undestanding about question

He want to delete a record in Table A using a value from Table B or vise varsa.

singe Query means in between EXEQ SQL and END EXEQ

If i am wrong, Please correct me

Regards
Raghu
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Thu Apr 12, 2007 12:58 pm    Post subject: Re: Deleting rows from multiple tables
Reply with quote

ragganga wrote:

Have you read question properly what vighnesh asked

Can i delete a particular row corresponding to a value from both tables using a single query ???

To my undestanding about question

He want to delete a record in Table A using a value from Table B or vise varsa.



No, I don't think so. He hasn't mentioned anything like 'vice versa'. That is your assumption. And if you read his note under "P.S.", the 'cascade' thing clearly means that he intends to delete from both tables.

Quote:

singe Query means in between EXEQ SQL and END EXEQ

If i am wrong, Please correct me

Regards
Raghu


That is not true.

Anyway, I doubt the original poster is going to come back and explain what he wants.
Back to top
View user's profile Send private message
ragganga

New User


Joined: 22 Jan 2007
Posts: 18
Location: bangalore

PostPosted: Thu Apr 12, 2007 1:41 pm    Post subject:
Reply with quote

P.S. don't need delete cascade as it will delete rows from all tables
Quote:


In 'P.S' Have you noticed 'Don't need delete Casacade' what it means and he already given the function of Cascade (as it will delete rows from all table)

My dear friend, what are writing

Wanderer wrote

Quote:
the 'cascade' thing clearly means that he intends to delete from both tables.


In 'P.S' he already mention in bracket he doesn't want to delete from other Table

(even the ones from which i don't want to delete it )


Thanks
Back to top
View user's profile Send private message
vighnesh_r

New User


Joined: 23 Aug 2006
Posts: 14

PostPosted: Thu Apr 19, 2007 5:10 pm    Post subject: Re: Deleting rows from multiple tables
Reply with quote

Hi All,

Sorry for the long absence icon_redface.gif

Perhaps i didn't explain it clearly enough. My intention was to delete rows from both tables A and B using a single query.
Back to top
View user's profile Send private message
vighnesh_r

New User


Joined: 23 Aug 2006
Posts: 14

PostPosted: Thu Apr 19, 2007 5:14 pm    Post subject: Re: Deleting rows from multiple tables
Reply with quote

wanderer wrote:
I don't think this is possible. Could you explain why you can't use two separate statements to delete from two different tables?


No particular reason ... My friend and i were having a discussion if such a thing could be done, thought i'd ask around icon_smile.gif
Back to top
View user's profile Send private message
vini_IBM

New User


Joined: 16 Apr 2007
Posts: 3
Location: Bangalore, INDIA

PostPosted: Thu Apr 19, 2007 8:10 pm    Post subject:
Reply with quote

As per my basic knowledge in SQL & DB2,

INSERT,UPDATE & DELETE* can be done on only one table in a given query. Conditions apply for DELETE when RI is there -icon_smile.gif

Please correct me if am wrong.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Unable to catalog a gdg dataset resid... Shovan JCL & VSAM 4 Fri Mar 24, 2017 2:24 pm
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts To know activities on db2 tables ashek15 DB2 4 Fri Mar 03, 2017 11:23 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us