RE: SQL delete question.

RE: SQL delete question.

 

  

And it works in DB2, version 8.2, Fixpack 14, on Windows 2003.

delete from employee2
where (empno, workdept) in
(select empno, workdept
from employee2)

------------------------------ Commands Entered
------------------------------
delete from employee2
where (empno, workdept) in
(select empno, workdept
from employee2);
------------------------------------------------------------------------
------
delete from employee2 where (empno, workdept) in (select empno, workdept
from employee)
DB20000I The SQL command completed successfully.






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-x96353760.[Email address protected]
Sent: Wednesday, June 27, 2007 9:33 AM
To: LazyDBA Discussion
Subject: RE: SQL delete question.

It does work on DB2 z/OS v7. Here's a quick test I did.

DELETE FROM ROB.PLAN_TABLE A
WHERE (A.QUERYNO,A.QBLOCKNO) IN (SELECT B.QUERYNO,B.QBLOCKNO FROM
ROB1.PLAN_TABLE B);

Robert Goethel
DBA
414-299-6237
rob.[Email address protected]

-----Original Message-----
From: Kent Olsen
[mailto:db2udbdba-ezmlmshield-x32606095.[Email address protected]
Sent: Wednesday, June 27, 2007 8:04 AM
To: LazyDBA Discussion
Subject: 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
>
>



---------------------------------------------------------------------
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