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
 

 

DB2 DELETE - multiple Tables.

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

New User


Joined: 28 Oct 2008
Posts: 98
Location: Cubicle

PostPosted: Mon Jun 04, 2012 5:47 pm    Post subject: DB2 DELETE - multiple Tables.
Reply with quote

Hi,

Question is - what are the ways to delete the rows from two DB2 Tables which don't have any referential integrity, so, in a way, mutaully exclusive but related to each other as follows:
Code:
SELECT  * FROM          M.table1 M1 ,           M.table2M2
WHERE M1.col_x = '1234' AND M1.col_y =  'abcd'           
AND M1.col1 = 'pgm'                             
AND M1.date1 > '2012-01-26'                                 
AND M1.col2 <>'  '                                   
AND M1.col3 = M2.col3
AND M1.col4 = M2.col4
AND M1.col5 = M2.col5
AND M1.col6 = M2.col6
AND M1.col7 = M2.col7
AND M1.col8 = M2.col8
when I execute this query, I get around 300 rows -- which I want to delete.

One way is using two different DELETE queries on these two tables. The second method I thought of using "WHERE EXISTS" as shown below:
Code:
DELETE FROM M.table M1 WHERE EXISTS
(SELECT  * FROM          M.table1 M1 ,           M.table2M2
WHERE M1.col_x = '1234' AND M1.col_y =  'abcd'           
AND M1.col1 = 'pgm'                             
AND M1.date1 > '2012-01-26'                                 
AND M1.col2 <>'  '                                   
AND M1.col3 = M2.col3
AND M1.col4 = M2.col4
AND M1.col5 = M2.col5
AND M1.col6 = M2.col6
AND M1.col7 = M2.col7
AND M1.col8 = M2.col8)
but,
1. It tries to DELETE much more than 300 rows.
2. I still issue only one table delete by using "DELETE FROM M.table M1", so how can I delete the data from 2nd table too, using a single query?
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Jun 04, 2012 6:33 pm    Post subject:
Reply with quote

without having db2 available referential restraints,
maybe a trigger?

your query would remain 1 query, just need another to do the work.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Tue Jun 05, 2012 1:42 pm    Post subject:
Reply with quote

The problem is you are using M1 twice :
this should delete all the requesrted rows out of M1
Rows in M2 will remain.
Code:
DELETE FROM M.table M1 WHERE
M1.col_x = '1234' AND M1.col_y =  'abcd'           
AND M1.col1 = 'pgm'                             
AND M1.date1 > '2012-01-26'                                 
AND M1.col2 <>'  '                                 
AND
EXISTS
(SELECT  * FROM    M.table2M2
WHERE    M1.col3 = M2.col3
AND M1.col4 = M2.col4
AND M1.col5 = M2.col5
AND M1.col6 = M2.col6
AND M1.col7 = M2.col7
AND M1.col8 = M2.col8)


Shame someone calling himself "DB2 Guy" doesn't know this
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2431
Location: Netherlands, Amstelveen

PostPosted: Tue Jun 05, 2012 2:03 pm    Post subject:
Reply with quote

Quote:

Shame someone calling himself "DB2 Guy" doesn't know this


Don't be that harsh, his nick is not "DB2 Master". Guy means probably youngster.
Back to top
View user's profile Send private message
DB2 Guy

New User


Joined: 28 Oct 2008
Posts: 98
Location: Cubicle

PostPosted: Tue Jun 05, 2012 2:28 pm    Post subject:
Reply with quote

Thanks GuyC.

Even with that you end up writing two differnt queries if you need to delete the rows from M2? That's why I asked for:
Quote:
2. I still issue only one table delete by using "DELETE FROM M.table M1", so how can I delete the data from 2nd table too, using a single query?
I did try what you show but didn't put it here in original post of mine -- I agree, that's fault on my part.

Thank God I did not use "DB2 DBA" as my avtar-name here. But then what's there in the name, sqlcode(1) usally solves problem on (DF)SORT, no! Okay, jokes apart...icon_smile.gif.

As an application developer, you're rather supposed to maintain things and not develop them and that bites you back - I'm reading SQL Cook Book by Graeme Birchall and I think it has got anough SQLs I'd ever need as a programer (not DBA) but then I'm still a Guy, as Peter says! icon_smile.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Tue Jun 05, 2012 6:12 pm    Post subject:
Reply with quote

You can only delete from 1 table in one query...
You can have DB2 do some extra stuff via Referential Integrity Cascade delete or via Triggers as dbz suggested.

you can delete from 1 table with one query, but then the data needed for knowing what to delete in the other table is probably gone.

If you need both tables deleted you'll probably end up with a program containing a cursor-loop with 2 deletes inside.

So in short, answering to your first question : YOU CAN'T.

PS.: to make sure, I even tried "delete from ... (select ... from old table (Delete ...) )" but as expected this doesn't work
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 Data replication from multiple Db2 ta... kishpra DB2 5 Mon Mar 27, 2017 9:58 pm
No new posts Unable to catalog a gdg dataset resid... Shovan JCL & VSAM 7 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


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