FAQ

On 20 Feb 96 at 15:32, Susan Tellier wrote :

Can anybody tell me if there's an easy way to remove rows from a table
based on rowid that refers to row_id in the exceptions table. (ex. I have
three rows in the EMP table that I simply want to remove that I've
found reference to in the exceptions table). I know the sqlplus solution by
feeding in the specific row id's but there's got to be a more efficient
way, I've got many tables and thousands of rows to clean out.
I'm sure I don't understand the question, because surely all you want to do is
this:

DELETE FROM emp
WHERE emp.rowid IN
(SELECT row_id
FROM exceptions);
Peter Moore - Database Administrator - MAT Transport Ltd, London, UK
pt_at_chaff.demon.co.uk : +44 (171) 410 6373
"With a little study you'll go a long ways & I wish you'd start now!"

Search Discussions

  • Anton Dischner at Feb 22, 1996 at 3:29 am
    Hi Susan, oracle gang

    this may help you to delete from big tables:

    declare

    emp_key emp.key%type; /* big table */
    exceptions_key exceptions.key%type;

    cursor sel is select key from exceptions;
    begin
    open sel;
    loop
    fetch sel into exceptions_key;
    exit when sel%notfound;
    delete from emp where exceptions_key = emp_key;
    commit;
    end loop;
    close sel;

    end;
    /

    Sorry, rowid is not a valid type.

    kind regards

    Toni

    +--------------------------------------------------------------------+
    Anton Dischner, DBA and system-programmer Phone: +49 89 70953202|
    Institut fuer Klinische Chemie Fax : +49 89 70958888|
    Klinikum Grosshadern Home : +49 89 6254060 |
    Ludwig Maximilians Universitaet Muenchen Handy: +49 172 8388880|
    81366 Muenchen Germany |
    Marchioninistr. 15 dischner_at_klch.med.uni-muenchen.de|
    +--------------------------------------------------------------------+

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 22, '96 at 2:39a
activeFeb 22, '96 at 3:29a
posts2
users2
websiteoracle.com

2 users in discussion

Peter Moore: 1 post Anton Dischner: 1 post

People

Translate

site design / logo © 2022 Grokbase