Hey Goethel Rob,
Did the code from Mustafa KESOGLU (see below) help?
-----Original Message-----
From: Edwards, Ed
Sent: Wednesday, June 27, 2007 5:22 AM
To: 'Goethel Rob '; LazyDBA Discussion
Subject: RE: SQL delete question.
Importance: High
Delete from target_table
where (batch#, seq#) in (select batch#, seq# from
keystodelete_table)
may be a simpler sql.
Selamlar/Best Regards.
Mustafa KESOGLU
IT Specialist, IBM Turk Ltd.
Phone : 90+212+317 10 98
Mobile : 90+556+317 10 98
Fax : 90+212+317 10 98
Notes : Mustafa [Email address protected]
e-Mail : [Email address protected]
"Edwards Ed " [Email address protected]
26.06.2007 18:22
To
"LazyDBA Discussion" [Email address protected]
cc
Subject
RE: SQL delete question.
Well, well, well!
It's Goethel Rob! Hehe. How have you been?
Try the following code.
DELETE FROM EMP2 E2
WHERE EMPNO =
(SELECT EMPNO
FROM EMP1 E1
WHERE E2.EMPNO = E1.EMPNO);
Please take a few minutes to provide feedback on the quality of service
you received from our staff. The Department of Education values your
feedback as a customer. Commissioner of Education Jeanine Blomberg is
committed to continuously assessing and improving the level and quality
of
services provided to you.Simply use the link below. Thank you in
advance
for completing the survey.
http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]
-----Original Message-----
From: Goethel Rob [mailto:db2udbdba-ezmlmshield-x16040074.[Email address
protected]
Sent: Tuesday, June 26, 2007 11:06 AM
To: LazyDBA Discussion
Subject: SQL delete question.
I know there's probably an easy answer to this one, but I can't think of
it today! Help!
How do you do a delete from a target table where the keys are in another
table.
Example:
Target table:
batch#, Seq#, data values
Keys to delete table:
batch#, Seq#
Batch# and Seq# are the key values and must be a concatenated key set.
I was thinking something like
Delete from target_table a
where a.batch# = (select batch# from keystodelete_table b where
a.batch#=b.batch# and a.seq#=b.seq#) and a.seq#=(select b.seq# from
keystodelete_table where a.batch#=b.batch# )
Thanks much.
Rob
************************************************************************
**************
This e-mail message and all attachments transmitted with it may contain
legally privileged and/or confidential information intended solely for
the use of the addressee(s). If the reader of this message is not the
intended recipient, you are hereby notified that any reading,
dissemination, distribution, copying, forwarding or other use of this
message or its attachments is strictly prohibited. If you have received
this message in error, please notify the sender immediately and delete
this message and all copies and backups thereof.
Thank you.
************************************************************************
**************
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
-----Original Message-----
From: Goethel Rob
[mailto:db2udbdba-ezmlmshield-x4910536.[Email address protected]
Sent: Tuesday, June 26, 2007 3:51 PM
To: LazyDBA Discussion
Subject: RE: SQL delete question.
Good afternoon Ed! I think your delete statement will not work because
it only uses a single key value of EMPNO. I have a two part key batch#,
seq#. I'm trying to speed up a delete flow that reads a extract file
and then does a delete on four tables. If I put the "extract" into a
table, then at least DB2 can do the work once instead of 4 index scans
(one for each table times #keys in extract file).
We could group the deletes into bunches (say read 50 records) and then
do a delete from table where (a.key1=:extractkey1 and
a.key2=:extractkey1a) or (a.key1=:extractkey2 and
a.key2=:extractkey2a)..... (key1=:extractkey50 and key2=:extractkey50a)
Thoughts?
Thanks!
-----Original Message-----
From: Edwards Ed
[mailto:db2udbdba-ezmlmshield-x89871259.[Email address protected]
Sent: Tuesday, June 26, 2007 10:23 AM
To: LazyDBA Discussion
Subject: RE: SQL delete question.
Well, well, well!
It's Goethel Rob! Hehe. How have you been?
Try the following code.
DELETE FROM EMP2 E2
WHERE EMPNO =
(SELECT EMPNO
FROM EMP1 E1
WHERE E2.EMPNO = E1.EMPNO);
Please take a few minutes to provide feedback on the quality of service
you received from our staff. The Department of Education values your
feedback as a customer. Commissioner of Education Jeanine Blomberg is
committed to continuously assessing and improving the level and quality
of services provided to you.Simply use the link below. Thank you in
advance for completing the survey.
http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]
-----Original Message-----
From: Goethel Rob
[mailto:db2udbdba-ezmlmshield-x16040074.[Email address protected]
Sent: Tuesday, June 26, 2007 11:06 AM
To: LazyDBA Discussion
Subject: SQL delete question.
I know there's probably an easy answer to this one, but I can't think of
it today! Help!
How do you do a delete from a target table where the keys are in another
table.
Example:
Target table:
batch#, Seq#, data values
Keys to delete table:
batch#, Seq#
Batch# and Seq# are the key values and must be a concatenated key set.
I was thinking something like
Delete from target_table a
where a.batch# = (select batch# from keystodelete_table b where
a.batch#=b.batch# and a.seq#=b.seq#) and a.seq#=(select b.seq# from
keystodelete_table where a.batch#=b.batch# )
Thanks much.
Rob
************************************************************************
**************
This e-mail message and all attachments transmitted with it may contain
legally privileged and/or confidential information intended solely for
the use of the addressee(s). If the reader of this message is not the
intended recipient, you are hereby notified that any reading,
dissemination, distribution, copying, forwarding or other use of this
message or its attachments is strictly prohibited. If you have received
this message in error, please notify the sender immediately and delete
this message and all copies and backups thereof.
Thank you.
************************************************************************
**************
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
************************************************************************
**************
This e-mail message and all attachments transmitted with it may contain
legally privileged and/or confidential information intended solely for
the use of the addressee(s). If the reader of this message is not the
intended recipient, you are hereby notified that any reading,
dissemination, distribution, copying, forwarding or other use of this
message or its attachments is strictly prohibited. If you have received
this message in error, please notify the sender immediately and delete
this message and all copies and backups thereof.
Thank you.
************************************************************************
**************
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page