RE: SQL delete question.

RE: SQL delete question.

 

  



What flavor of DB2 is this?

I believe that this syntax is a SQL/Server or Oracle item that is not supported in UDB/LUW.


Kent




>
>Going to give it a try! Wouldn't you know it, there's always an easier
>solution! Just could not rack my brain!
>
>Take care Ed.
>
>
>Robert Goethel
>DBA
>414-299-6237
>rob.[Email address protected]
>
>-----Original Message-----
>From: Edwards Ed
>[mailto:db2udbdba-ezmlmshield-x53609240.[Email address protected]
>Sent: Wednesday, June 27, 2007 7:54 AM
>To: LazyDBA Discussion
>Subject: RE: SQL delete question.
>
>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
>
>
>---------------------------------------------------------------------
>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