|
View previous topic :: View next topic
|
| Author |
Message |
DB2 Guy
New User
Joined: 28 Oct 2008
Posts: 98
Location: Cubicle
|
|
|
|
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 |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
| 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 |
|
|
DB2 Guy
New User
Joined: 28 Oct 2008
Posts: 98
Location: Cubicle
|
|
|
|
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... .
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! |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
|
|
