Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Alternative way to write delete query

Re: Alternative way to write delete query

From: Mogens Nørgaard <mln_at_miracleas.dk>
Date: Wed, 28 May 2003 05:10:05 -0800
Message-ID: <F001.005A40CF.20030528051005@fatcity.com>


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"
> <ineyman_at_perceptr To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> on.com> cc:
> Sent by: Subject: RE: Alternative way to write delete query
> root_at_fatcity.com
>
>
> 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
>ineyman_at_perceptron.com
>
>
>
>-----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: mrichard_at_transurban.com.au
>
>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: ListGuru_at_fatcity.com (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: sollig_at_lifetouch.com
>
>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: ListGuru_at_fatcity.com (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: ineyman_at_perceptron.com
>
>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: ListGuru_at_fatcity.com (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).
>
>
>
>
><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> 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: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
  INET: mln_at_miracleas.dk

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: ListGuru_at_fatcity.com (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).
Received on Wed May 28 2003 - 08:10:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US