Re: Alternative way to write delete query
delete from table_a a where exists ( select null from table_b b where a.column = b.col_a or a.column = b.col_b ) Have Fun :) Mark Richard wrote: Hi List, I'm having a mental blank and looking for suggestions... I'm trying to remember alternative ways to write the below query: delete from table where column in (select col_a from table_b union all select col_b from table_b); Having said that, I don't mind if you simplify the nested query down to select col_a from table_b even. I just have a feeling that I've seen an equivalent query written totally differently but I can't remember how. I have a feeling it effectively allows more than one table to be mentioned in the delete without needing a nested query (like the options available in normal selects). Any suggestions? Thanks, Mark. Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chip INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Alternative way to write delete query
If anybody knows, it must be Lex... Lex, you genius of geniuses and SQL Logician of them all - can you help here? Mogens Mark Richard wrote: This is quite close to what I was thinking of however it doesn't seem to work for Oracle. Does anyone know if there is similar syntax available in the Oracle world? I've looked at the Oracle (8.1.7) doco but can't see how I can achieve what I want to do. "Igor Neyman" [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] on.com cc: Sent by: Subject: RE: Alternative way to write delete query [EMAIL PROTECTED] 28/05/2003 02:34 Please respond to ORACLE-L Small correction for SQL Server / Sybase, if anyone cares, of course -:) Delete table1 from table_a where column1 = col_a or column1 = col_b Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- OLLIG Sent: Tuesday, May 27, 2003 9:35 AM To: Multiple recipients of list ORACLE-L Mark - you could also do it with 2 deletes something like this: delete from (select column1 from table1 , table_a where column1 = col_a) delete from (select column1 from table1 , table_b where column1 = col_b) couldn't find a way to avoid the "ORA-01752: cannot delete from view without exactly one key-preserved table" with the or condition. perhaps someone who isn't still clearing the brain cobwebs after a long weekend can see a solution there. FWIW - i'm pretty sure SQL Server Sybase will let you get by with this: delete from table1 , table_a , table_b where column1 = col_a or column1 = col_b (don't have a sandbox handy to confirm though) maybe that's what you were thinking of? -Original Message- Sent: Tuesday, May 27, 2003 12:27 AM To: Multiple recipients of list ORACLE-L Hi List, I'm having a mental blank and looking for suggestions... I'm trying to remember alternative ways to write the below query: delete from table where column in (select col_a from table_b union all select col_b from table_b); Having said that, I don't mind if you simplify the nested query down to "select col_a from table_b" even. I just have a feeling that I've seen an equivalent query written totally differently but I can't remember how. I have a feeling it effectively allows more than one table to be mentioned in the delete without needing a nested query (like the options available in normal selects). Any suggestions? Thanks, Mark. Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood
RE: Alternative way to write delete query
delete table1 where exists ( select 1 from ( select column from table_a union all select column from table_b ) a where a.column = table1.column ); -Original Message- Sent: Wednesday, May 28, 2003 9:10 AM To: Multiple recipients of list ORACLE-L If anybody knows, it must be Lex... Lex, you genius of geniuses and SQL Logician of them all - can you help here? Mogens Mark Richard wrote: This is quite close to what I was thinking of however it doesn't seem to work for Oracle. Does anyone know if there is similar syntax available in the Oracle world? I've looked at the Oracle (8.1.7) doco but can't see how I can achieve what I want to do. Igor Neyman [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] on.com cc: Sent by: Subject: RE: Alternative way to write delete query [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 28/05/2003 02:34 Please respond to ORACLE-L Small correction for SQL Server / Sybase, if anyone cares, of course -:) Delete table1 from table_a where column1 = col_a or column1 = col_b Igor Neyman, OCP DBA [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- OLLIG Sent: Tuesday, May 27, 2003 9:35 AM To: Multiple recipients of list ORACLE-L Mark - you could also do it with 2 deletes something like this: delete from (select column1 from table1 , table_a where column1 = col_a) delete from (select column1 from table1 , table_b where column1 = col_b) couldn't find a way to avoid the ORA-01752: cannot delete from view without exactly one key-preserved table with the or condition. perhaps someone who isn't still clearing the brain cobwebs after a long weekend can see a solution there. FWIW - i'm pretty sure SQL Server Sybase will let you get by with this: delete from table1 , table_a , table_b where column1 = col_a or column1 = col_b (don't have a sandbox handy to confirm though) maybe that's what you were thinking of? -Original Message- Sent: Tuesday, May 27, 2003 12:27 AM To: Multiple recipients of list ORACLE-L Hi List, I'm having a mental blank and looking for suggestions... I'm trying to remember alternative ways to write the below query: delete from table where column in (select col_a from table_b union all select col_b from table_b); Having said that, I don't mind if you simplify the nested query down to select col_a from table_b even. I just have a feeling that I've seen an equivalent query written totally differently but I can't remember how. I have a feeling it effectively allows more than one table to be mentioned in the delete without needing a nested query (like the options available in normal selects). Any suggestions? Thanks, Mark. Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net http://www.orafaq.net -- Author: Mark Richard INET: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see
RE: Alternative way to write delete query
Small correction for SQL Server / Sybase, if anyone cares, of course -:) Delete table1 from table_a where column1 = col_a or column1 = col_b Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- OLLIG Sent: Tuesday, May 27, 2003 9:35 AM To: Multiple recipients of list ORACLE-L Mark - you could also do it with 2 deletes something like this: delete from (select column1 from table1 , table_a where column1 = col_a) delete from (select column1 from table1 , table_b where column1 = col_b) couldn't find a way to avoid the ORA-01752: cannot delete from view without exactly one key-preserved table with the or condition. perhaps someone who isn't still clearing the brain cobwebs after a long weekend can see a solution there. FWIW - i'm pretty sure SQL Server Sybase will let you get by with this: delete from table1 , table_a , table_b where column1 = col_a or column1 = col_b (don't have a sandbox handy to confirm though) maybe that's what you were thinking of? -Original Message- Sent: Tuesday, May 27, 2003 12:27 AM To: Multiple recipients of list ORACLE-L Hi List, I'm having a mental blank and looking for suggestions... I'm trying to remember alternative ways to write the below query: delete from table where column in (select col_a from table_b union all select col_b from table_b); Having said that, I don't mind if you simplify the nested query down to select col_a from table_b even. I just have a feeling that I've seen an equivalent query written totally differently but I can't remember how. I have a feeling it effectively allows more than one table to be mentioned in the delete without needing a nested query (like the options available in normal selects). Any suggestions? Thanks, Mark. Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: STEVE OLLIG INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Alternative way to write delete query
This is quite close to what I was thinking of however it doesn't seem to work for Oracle. Does anyone know if there is similar syntax available in the Oracle world? I've looked at the Oracle (8.1.7) doco but can't see how I can achieve what I want to do. Igor Neyman [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] on.com cc: Sent by: Subject: RE: Alternative way to write delete query [EMAIL PROTECTED] 28/05/2003 02:34 Please respond to ORACLE-L Small correction for SQL Server / Sybase, if anyone cares, of course -:) Delete table1 from table_a where column1 = col_a or column1 = col_b Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- OLLIG Sent: Tuesday, May 27, 2003 9:35 AM To: Multiple recipients of list ORACLE-L Mark - you could also do it with 2 deletes something like this: delete from (select column1 from table1 , table_a where column1 = col_a) delete from (select column1 from table1 , table_b where column1 = col_b) couldn't find a way to avoid the ORA-01752: cannot delete from view without exactly one key-preserved table with the or condition. perhaps someone who isn't still clearing the brain cobwebs after a long weekend can see a solution there. FWIW - i'm pretty sure SQL Server Sybase will let you get by with this: delete from table1 , table_a , table_b where column1 = col_a or column1 = col_b (don't have a sandbox handy to confirm though) maybe that's what you were thinking of? -Original Message- Sent: Tuesday, May 27, 2003 12:27 AM To: Multiple recipients of list ORACLE-L Hi List, I'm having a mental blank and looking for suggestions... I'm trying to remember alternative ways to write the below query: delete from table where column in (select col_a from table_b union all select col_b from table_b); Having said that, I don't mind if you simplify the nested query down to select col_a from table_b even. I just have a feeling that I've seen an equivalent query written totally differently but I can't remember how. I have a feeling it effectively allows more than one table to be mentioned in the delete without needing a nested query (like the options available in normal selects). Any suggestions? Thanks, Mark. Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: Delete query...question.
I was just wondering... Specifically, on the DELETE's WHERE clause, isn't it better to use ROWID versus CREATED?? If you use ROWID then you don't have to walk the index. DELETE FROM tbl WHERE ROWID. If you use CREATED then you have to walk the index, again? Right? DELETE FROM tbl WHERE created To head off emails, I know your supposed to be very careful when working with ROWID, i.e. not storing rowid for future reference, etc. But this specific example seems to be ok. Thoughts? -Original Message- Sent: Tuesday, July 09, 2002 1:54 PM To: Multiple recipients of list ORACLE-L delete from tbl where rowid in ( select rowid from tbl where user_id = user and rownum =1 order by date asc ) -bp - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 09, 2002 9:59 AM Hi, Can anyone help me with this delete statement? I have the following table: Name Null?Type - - USER_ID NOT NULL NUMBER(15) PASSWORD VARCHAR2(30) CREATEDDATE What I need to do is delete the oldest record for a particular user_id. And its doing my head in. Any help appreciated! Thanks, Steve. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: BigP INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Delete query...question.
any ways when u use order by clause it will surely slow down. rgds, Ams. -Original Message- Chris Sent: Wednesday, July 10, 2002 7:33 PM To: Multiple recipients of list ORACLE-L I was just wondering... Specifically, on the DELETE's WHERE clause, isn't it better to use ROWID versus CREATED?? If you use ROWID then you don't have to walk the index. DELETE FROM tbl WHERE ROWID. If you use CREATED then you have to walk the index, again? Right? DELETE FROM tbl WHERE created To head off emails, I know your supposed to be very careful when working with ROWID, i.e. not storing rowid for future reference, etc. But this specific example seems to be ok. Thoughts? -Original Message- Sent: Tuesday, July 09, 2002 1:54 PM To: Multiple recipients of list ORACLE-L delete from tbl where rowid in ( select rowid from tbl where user_id = user and rownum =1 order by date asc ) -bp - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 09, 2002 9:59 AM Hi, Can anyone help me with this delete statement? I have the following table: Name Null?Type - - USER_ID NOT NULL NUMBER(15) PASSWORD VARCHAR2(30) CREATEDDATE What I need to do is delete the oldest record for a particular user_id. And its doing my head in. Any help appreciated! Thanks, Steve. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: BigP INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Amjad INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Delete query
Hi, Can anyone help me with this delete statement? I have the following table: Name Null?Type - - USER_ID NOT NULL NUMBER(15) PASSWORD VARCHAR2(30) CREATEDDATE What I need to do is delete the oldest record for a particular user_id. And its doing my head in. Any help appreciated! Thanks, Steve. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Delete query
try: delete tab where (user_id, created) in (select user_id, min(created) from tab group by user_id); hth, jack --- Steven Hovington [EMAIL PROTECTED] wrote: Hi, Can anyone help me with this delete statement? I have the following table: Name Null? Type - - USER_ID NOT NULL NUMBER(15) PASSWORD VARCHAR2(30) CREATED DATE What I need to do is delete the oldest record for a particular user_id. And its doing my head in. Any help appreciated! Thanks, Steve. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Delete query
delete from my_table where (user_id, created) in (select user_id, min(created) from my_table where user_id = 'YOUR_VALUE_HERE' group by user_id); Please do a sample test in a test database ... you have been warned. YMMV Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, July 09, 2002 1:00 PM To: Multiple recipients of list ORACLE-L Hi, Can anyone help me with this delete statement? I have the following table: Name Null?Type - - USER_ID NOT NULL NUMBER(15) PASSWORD VARCHAR2(30) CREATEDDATE What I need to do is delete the oldest record for a particular user_id. And its doing my head in. Any help appreciated! Thanks, Steve. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Delete query
Well, if you don't need to worry about duplicate created dates, this should work: delete from tablex where user_id=:b1 and created=(select min(created) from tablex where user_id=:b1); Kevin Kennedy First Point Energy Corporation -Original Message- Sent: Tuesday, July 09, 2002 10:00 AM To: Multiple recipients of list ORACLE-L Hi, Can anyone help me with this delete statement? I have the following table: Name Null?Type - - USER_ID NOT NULL NUMBER(15) PASSWORD VARCHAR2(30) CREATEDDATE What I need to do is delete the oldest record for a particular user_id. And its doing my head in. Any help appreciated! Thanks, Steve. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kkennedy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Delete query
How about: delete from table where userid = 'whatever' and created = select min(created) from table where userid = 'whatever'; Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 09, 2002 6:59 PM Hi, Can anyone help me with this delete statement? I have the following table: Name Null?Type - - USER_ID NOT NULL NUMBER(15) PASSWORD VARCHAR2(30) CREATEDDATE What I need to do is delete the oldest record for a particular user_id. And its doing my head in. Any help appreciated! Thanks, Steve. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Delete query
delete from blah b where created = (select min(created) from blah where user_id = b.user_id); Just be aware that if there is only 1 record for a user, this statement would delete that. I say that just in case you wanted to keep at least 1 record for a particular user (just reading into what you're really doing). - Jeff -Original Message- Sent: Tuesday, July 09, 2002 1:00 PM To: Multiple recipients of list ORACLE-L Hi, Can anyone help me with this delete statement? I have the following table: Name Null?Type - - USER_ID NOT NULL NUMBER(15) PASSWORD VARCHAR2(30) CREATEDDATE What I need to do is delete the oldest record for a particular user_id. And its doing my head in. Any help appreciated! Thanks, Steve. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Young, Jeff A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Delete query
delete from tbl where rowid in ( select rowid from tbl where user_id = user and rownum =1 order by date asc ) -bp - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 09, 2002 9:59 AM Hi, Can anyone help me with this delete statement? I have the following table: Name Null?Type - - USER_ID NOT NULL NUMBER(15) PASSWORD VARCHAR2(30) CREATEDDATE What I need to do is delete the oldest record for a particular user_id. And its doing my head in. Any help appreciated! Thanks, Steve. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: BigP INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Delete query
To avoid aggregates you can : delete from table t where not exists (select null from table x where t.USER_ID=x.USER_ID and t.CREATED x.CREATED); it seems to run faster given the USER_ID and CREATED are indexed -Original Message- Sent: Tuesday, July 09, 2002 1:00 PM To: Multiple recipients of list ORACLE-L Hi, Can anyone help me with this delete statement? I have the following table: Name Null?Type - - USER_ID NOT NULL NUMBER(15) PASSWORD VARCHAR2(30) CREATEDDATE What I need to do is delete the oldest record for a particular user_id. And its doing my head in. Any help appreciated! Thanks, Steve. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vadim Gorbounov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Delete query
Do you want to delete the oldest record for a given userid (many answers provided) or the oldest record for each userid (a little more work)? Steven HovingtonTo: Multiple recipients of list ORACLE-L steven.hovin[EMAIL PROTECTED] gton cc: @procession.cSubject: Delete query om Sent by: root 07/09/2002 12:59 PM Please respond to ORACLE-L Hi, Can anyone help me with this delete statement? I have the following table: Name Null?Type - - USER_ID NOT NULL NUMBER(15) PASSWORD VARCHAR2(30) CREATEDDATE What I need to do is delete the oldest record for a particular user_id. And its doing my head in. Any help appreciated! Thanks, Steve. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Delete query
Vadim, excellent answer. answers the question and the underlying intent, and takes into account performance. jack --- Vadim Gorbounov [EMAIL PROTECTED] wrote: To avoid aggregates you can : delete from table t where not exists (select null from table x where t.USER_ID=x.USER_ID and t.CREATED x.CREATED); it seems to run faster given the USER_ID and CREATED are indexed -Original Message- Sent: Tuesday, July 09, 2002 1:00 PM To: Multiple recipients of list ORACLE-L Hi, Can anyone help me with this delete statement? I have the following table: Name Null? Type - - USER_ID NOT NULL NUMBER(15) PASSWORD VARCHAR2(30) CREATED DATE What I need to do is delete the oldest record for a particular user_id. And its doing my head in. Any help appreciated! Thanks, Steve. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vadim Gorbounov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Delete query
To delete the oldest record for each userid (keep record if there is only one) delete from table a where created = (select min(created) from table b where b.user_id = a.user_id having count(*) 1); --Jeff -Original Message- Sent: Tuesday, July 09, 2002 11:00 AM To: Multiple recipients of list ORACLE-L Hi, Can anyone help me with this delete statement? I have the following table: Name Null?Type - - USER_ID NOT NULL NUMBER(15) PASSWORD VARCHAR2(30) CREATEDDATE What I need to do is delete the oldest record for a particular user_id. And its doing my head in. Any help appreciated! Thanks, Steve. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eberhard, Jeff INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Delete query
At 10:25:44 09 Jul 2002 -0800, Thomas Day wrote: Do you want to delete the oldest record for a given userid (many answers provided) or the oldest record for each userid (a little more work)? Thomas, I wanted to delete just the oldest (by date) record for a given user id. Thanks for all the replies everyone, very helpful indeed. Steven. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
