|
View previous topic :: View next topic
|
| Author |
Message |
rahul.arora
New User
Joined: 09 Jul 2011
Posts: 9
Location: India
|
|
|
|
I want to delete data from a table. Table has columns ID and access group.One Id may be associated to one group or many groups.
I want to use query for deletion is
Select * from tablename
where ID='12345'
This query deletes the all groups associated with that ID.
The thing is I want to insert all the deleted ID-groups mappings in the audit table.
Can anyone suggest the solution with using cursors? |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
why have you not thought about
insert into audit_table
select id
, access_group
from tablename
where ID='12345'
before you do the delete? |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| Whilst I'm not a DBA or a DB2 maven, is this not a situation where a trigger could be used to good purpose? |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Quote: |
is this not a situation where a trigger could be used to good purpose?
|
Trigger may be a overhead to the processing when the data to be deleted is large and so I feel Datapropagator is better than trigger when you have huge data to process
Refer the manual here |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
For my $.02, a very simple and effective way to do this is the way DBZ has suggested. . . |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Very much agreed Dick  |
|
| Back to top |
|
|
rahul.arora
New User
Joined: 09 Jul 2011
Posts: 9
Location: India
|
|
|
|
Hello dbzTHEdinosauer,
I believe this will only insert the first id -access group mapping.
suppose we have 4 access groups mapped to single Id in table
ID Access Group
12345 group1
12345 group2
12345 group3
12345 group4
Now we use this query before delete
insert into audit_table
select id
, access_group
from tablename
where ID='12345'
this will insert only 1st Id-access group mapping in the the audit table but delete command will delete the all four mappings....
Please correct me if I am wrong.. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
pllllllllleeeeeeeeeeeeeeeeesssssssssssssee.
why don't you experiment with spufi and then come back.
why do you think a select that is part of an insert would function differently than a standalone select? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| you forgot the aaaaaaaaaaaaaaaa |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
declare a cursor on the table to have deletes,
with an open FOR UPDATE clause
as you fetch each row,
insert what you want in the audit using the fetch-populated host variables,
then delete where current cursor
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaah. |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| Quote: |
query for deletion is
Select * from tablename
where ID='12345' |
That seems to be a selection not a deletion. And is this not the same topic as posted on another board? |
|
| Back to top |
|
|
|
|
